{"id":26983,"date":"2023-07-27T11:29:50","date_gmt":"2023-07-27T09:29:50","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/?p=26983"},"modified":"2023-07-27T11:51:02","modified_gmt":"2023-07-27T09:51:02","slug":"generated-columns-in-postgresql","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/generated-columns-in-postgresql\/","title":{"rendered":"Generated columns in PostgreSQL"},"content":{"rendered":"\n<p>Sometimes it becomes handy when you can auto-generate a column based on an expression. For this there is the concept of generated column. Those columns can be either of type &#8220;stored&#8221; (the derived value is computed when the column is written) or &#8220;virtual&#8221; (the derived value is computed when the column is being read). Currently PostgreSQL only supports the &#8220;stored&#8221; generated columns, but there are certainly use cases for this. Let&#8217;s have a look at how that looks like.<\/p>\n\n\n\n<p>As usual, we&#8217;ll start with a simple table to play with:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; highlight: [1,3]; title: ; notranslate\" title=\"\">\npostgres=# create table t1 ( product text, price int, amount_sold int, amount_returned int ); \nCREATE TABLE\npostgres=# insert into t1 values (&#039;product1&#039;, 5, 1000, 10 );\nINSERT 0 1\npostgres=# \n<\/pre><\/div>\n\n\n<p>What we can use a generated column for is to auto-calculate the total price for all sold products. Without a generated column we would need a trigger for that (for simplicity will use integers for that, we do not need to be that precise here):<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; highlight: [1,3]; title: ; notranslate\" title=\"\">\npostgres=# alter table t1 add column total_price int generated always as (price*amount_sold) stored;\nALTER TABLE\npostgres=# \\d t1\n                                          Table &quot;public.t1&quot;\n     Column      |  Type   | Collation | Nullable |                     Default                      \n-----------------+---------+-----------+----------+--------------------------------------------------\n product         | text    |           |          | \n price           | integer |           |          | \n amount_sold     | integer |           |          | \n amount_returned | integer |           |          | \n total_price     | integer |           |          | generated always as (price * amount_sold) stored\n<\/pre><\/div>\n\n\n<p>Having that in place, the total price is automatically calculated and there nothing else to do:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; highlight: [1]; title: ; notranslate\" title=\"\">\npostgres=# select total_price from t1;\n total_price \n-------------\n        5000\n(1 row)\n<\/pre><\/div>\n\n\n<p>Another use case is to automatically calculate the percentage of returned products:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; highlight: [1,3]; title: ; notranslate\" title=\"\">\npostgres=# alter table t1 add column percentage_returned int generated always as (amount_returned\/(amount_sold\/100)) stored;\nALTER TABLE\npostgres=# select percentage_returned from t1;\n percentage_returned \n---------------------\n                   1\n(1 row)\n\npostgres=# \n<\/pre><\/div>\n\n\n<p>For new (and updated) products this works, of course, as well:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; highlight: [1,3]; title: ; notranslate\" title=\"\">\npostgres=# insert into t1 values (&#039;product2&#039;, 33, 800, 600 );\nINSERT 0 1\npostgres=# select * from t1;\n product  | price | amount_sold | amount_returned | total_price | percentage_returned \n----------+-------+-------------+-----------------+-------------+---------------------\n product1 |     5 |        1000 |              10 |        5000 |                   1\n product2 |    33 |         800 |             600 |       26400 |                  75\n(2 rows)\n<\/pre><\/div>\n\n\n<p>Updating generated columns will through an error, which is how it must work:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; highlight: [1]; title: ; notranslate\" title=\"\">\npostgres=# update t1 set percentage_returned = 0;\nERROR:  column &quot;percentage_returned&quot; can only be updated to DEFAULT\nDETAIL:  Column &quot;percentage_returned&quot; is a generated column.\n<\/pre><\/div>\n\n\n<p>You can even combine that with column level privileges to give other users or roles just access to these generated columns, but not the base columns:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; highlight: [1,3,5,7,14]; title: ; notranslate\" title=\"\">\npostgres=# create user u with password &#039;u&#039;;\nCREATE ROLE\npostgres=# grant select(total_price,percentage_returned) on t1 to u;\nGRANT\npostgres=# \\c postgres u\nYou are now connected to database &quot;postgres&quot; as user &quot;u&quot;.\npostgres=&gt; select total_price,percentage_returned from t1;\n total_price | percentage_returned \n-------------+---------------------\n        5000 |                   1\n       26400 |                  75\n(2 rows)\n\npostgres=&gt; select product from t1;\nERROR:  permission denied for table t1\npostgres=&gt; \n<\/pre><\/div>\n\n\n<p>What you cannot do, is to create a generated column which uses another generated column in the expression:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; highlight: [1,3]; title: ; notranslate\" title=\"\">\npostgres=# \\c postgres postgres\nYou are now connected to database &quot;postgres&quot; as user &quot;postgres&quot;.\npostgres=# alter table t1 add column money_lost int generated always as (total_price-((total_price\/100)*percentage_returned)) stored;\nERROR:  cannot use generated column &quot;total_price&quot; in column generation expression\nDETAIL:  A generated column cannot reference another generated column.\npostgres=# \n<\/pre><\/div>\n\n\n<p>A nice little feature for plenty of use cases.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Sometimes it becomes handy when you can auto-generate a column based on an expression. For this there is the concept of generated column. Those columns can be either of type &#8220;stored&#8221; (the derived value is computed when the column is written) or &#8220;virtual&#8221; (the derived value is computed when the column is being read). Currently [&hellip;]<\/p>\n","protected":false},"author":29,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229,198],"tags":[77],"type_dbi":[],"class_list":["post-26983","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","category-database-management","tag-postgresql"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.2) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>Generated columns in PostgreSQL - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/generated-columns-in-postgresql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Generated columns in PostgreSQL\" \/>\n<meta property=\"og:description\" content=\"Sometimes it becomes handy when you can auto-generate a column based on an expression. For this there is the concept of generated column. Those columns can be either of type &#8220;stored&#8221; (the derived value is computed when the column is written) or &#8220;virtual&#8221; (the derived value is computed when the column is being read). Currently [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/generated-columns-in-postgresql\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2023-07-27T09:29:50+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-07-27T09:51:02+00:00\" \/>\n<meta name=\"author\" content=\"Daniel Westermann\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@westermanndanie\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Daniel Westermann\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/generated-columns-in-postgresql\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/generated-columns-in-postgresql\/\"},\"author\":{\"name\":\"Daniel Westermann\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"headline\":\"Generated columns in PostgreSQL\",\"datePublished\":\"2023-07-27T09:29:50+00:00\",\"dateModified\":\"2023-07-27T09:51:02+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/generated-columns-in-postgresql\/\"},\"wordCount\":246,\"commentCount\":1,\"keywords\":[\"PostgreSQL\"],\"articleSection\":[\"Database Administration &amp; Monitoring\",\"Database management\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/generated-columns-in-postgresql\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/generated-columns-in-postgresql\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/generated-columns-in-postgresql\/\",\"name\":\"Generated columns in PostgreSQL - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2023-07-27T09:29:50+00:00\",\"dateModified\":\"2023-07-27T09:51:02+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/generated-columns-in-postgresql\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/generated-columns-in-postgresql\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/generated-columns-in-postgresql\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Generated columns in PostgreSQL\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\",\"name\":\"Daniel Westermann\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"caption\":\"Daniel Westermann\"},\"description\":\"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &amp;\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.\",\"sameAs\":[\"https:\/\/x.com\/westermanndanie\"],\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Generated columns in PostgreSQL - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/generated-columns-in-postgresql\/","og_locale":"en_US","og_type":"article","og_title":"Generated columns in PostgreSQL","og_description":"Sometimes it becomes handy when you can auto-generate a column based on an expression. For this there is the concept of generated column. Those columns can be either of type &#8220;stored&#8221; (the derived value is computed when the column is written) or &#8220;virtual&#8221; (the derived value is computed when the column is being read). Currently [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/generated-columns-in-postgresql\/","og_site_name":"dbi Blog","article_published_time":"2023-07-27T09:29:50+00:00","article_modified_time":"2023-07-27T09:51:02+00:00","author":"Daniel Westermann","twitter_card":"summary_large_image","twitter_creator":"@westermanndanie","twitter_misc":{"Written by":"Daniel Westermann","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/generated-columns-in-postgresql\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/generated-columns-in-postgresql\/"},"author":{"name":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"headline":"Generated columns in PostgreSQL","datePublished":"2023-07-27T09:29:50+00:00","dateModified":"2023-07-27T09:51:02+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/generated-columns-in-postgresql\/"},"wordCount":246,"commentCount":1,"keywords":["PostgreSQL"],"articleSection":["Database Administration &amp; Monitoring","Database management"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/generated-columns-in-postgresql\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/generated-columns-in-postgresql\/","url":"https:\/\/www.dbi-services.com\/blog\/generated-columns-in-postgresql\/","name":"Generated columns in PostgreSQL - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2023-07-27T09:29:50+00:00","dateModified":"2023-07-27T09:51:02+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/generated-columns-in-postgresql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/generated-columns-in-postgresql\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/generated-columns-in-postgresql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Generated columns in PostgreSQL"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66","name":"Daniel Westermann","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","caption":"Daniel Westermann"},"description":"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &amp;\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.","sameAs":["https:\/\/x.com\/westermanndanie"],"url":"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/26983","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/29"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=26983"}],"version-history":[{"count":12,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/26983\/revisions"}],"predecessor-version":[{"id":26999,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/26983\/revisions\/26999"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=26983"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=26983"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=26983"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=26983"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}