{"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:29:50","modified_gmt":"2023-07-27T09:29:50","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 class=\"wp-block-paragraph\">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 class=\"wp-block-paragraph\">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 class=\"wp-block-paragraph\">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 class=\"wp-block-paragraph\">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 class=\"wp-block-paragraph\">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 class=\"wp-block-paragraph\">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 class=\"wp-block-paragraph\">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 class=\"wp-block-paragraph\">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 class=\"wp-block-paragraph\">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 class=\"wp-block-paragraph\">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;&nbsp;<\/p>\n","protected":false},"author":28,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"neve_meta_sidebar":"","neve_meta_container":"","neve_meta_enable_content_width":"","neve_meta_content_width":0,"neve_meta_title_alignment":"","neve_meta_author_avatar":"","neve_post_elements_order":"","neve_meta_disable_header":"","neve_meta_disable_footer":"","neve_meta_disable_title":"","neve_meta_reading_time":"","_themeisle_gutenberg_block_has_review":false,"footnotes":""},"categories":[4133],"tags":[],"class_list":["post-26983","post","type-post","status-publish","format-standard","category-database"],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v28.8 (Yoast SEO v28.8) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>Generated columns in PostgreSQL - dbi services 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;&nbsp;\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/generated-columns-in-postgresql\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi services Blog\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/dbiservices\/\" \/>\n<meta property=\"article:published_time\" content=\"2023-07-27T09:29:50+00:00\" \/>\n<meta name=\"author\" content=\"Open source Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@dbiservices\" \/>\n<meta name=\"twitter:site\" content=\"@dbiservices\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Open source Team\" \/>\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\":\"Open source Team\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/0b4548362cac3eeff8b3eaf329e3530a\"},\"headline\":\"Generated columns in PostgreSQL\",\"datePublished\":\"2023-07-27T09:29:50+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/generated-columns-in-postgresql\\\/\"},\"wordCount\":246,\"commentCount\":1,\"publisher\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/#organization\"},\"articleSection\":[\"Database\"],\"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 services Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2023-07-27T09:29:50+00:00\",\"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\":\"Home\",\"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 services Blog\",\"description\":\"#BeSharing\",\"publisher\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/#organization\"},\"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\":\"Organization\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/#organization\",\"name\":\"dbi services\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/logo\\\/image\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2026\\\/09\\\/dbi-services-logo.png\",\"contentUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2026\\\/09\\\/dbi-services-logo.png\",\"width\":512,\"height\":512,\"caption\":\"dbi services\"},\"image\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/logo\\\/image\\\/\"},\"sameAs\":[\"https:\\\/\\\/www.facebook.com\\\/dbiservices\\\/\",\"https:\\\/\\\/x.com\\\/dbiservices\",\"https:\\\/\\\/www.linkedin.com\\\/company\\\/dbi-services\\\/\",\"https:\\\/\\\/www.youtube.com\\\/user\\\/dbiservices\"],\"description\":\"Swiss IT consulting and services company specialized in efficient data infrastructures and platforms: databases, application servers, cloud, DevOps, digitalization, analytics, data quality and monitoring. Headquartered in Del\u00e9mont with offices in Nyon, Basel, Bern and Zurich.\",\"email\":\"info@dbi-services.com\",\"telephone\":\"+41 32 422 96 00\",\"legalName\":\"dbi services SA\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/0b4548362cac3eeff8b3eaf329e3530a\",\"name\":\"Open source Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2022\\\/05\\\/open-source-author.png\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2022\\\/05\\\/open-source-author.png\",\"contentUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2022\\\/05\\\/open-source-author.png\",\"caption\":\"Open source Team\"},\"description\":\"Posts published under the Open source Team account are collective contributions by the dbi services open infrastructure consultants. The practice covers open-source databases and platforms, PostgreSQL first of all, along with MySQL, MariaDB, and Linux, from architecture, replication, and high availability to performance tuning and operations under service level agreements, and contributes actively to the PostgreSQL community.\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/open-source-team\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Generated columns in PostgreSQL - dbi services 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;&nbsp;","og_url":"https:\/\/www.dbi-services.com\/blog\/generated-columns-in-postgresql\/","og_site_name":"dbi services Blog","article_publisher":"https:\/\/www.facebook.com\/dbiservices\/","article_published_time":"2023-07-27T09:29:50+00:00","author":"Open source Team","twitter_card":"summary_large_image","twitter_creator":"@dbiservices","twitter_site":"@dbiservices","twitter_misc":{"Written by":"Open source Team","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":"Open source Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0b4548362cac3eeff8b3eaf329e3530a"},"headline":"Generated columns in PostgreSQL","datePublished":"2023-07-27T09:29:50+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/generated-columns-in-postgresql\/"},"wordCount":246,"commentCount":1,"publisher":{"@id":"https:\/\/www.dbi-services.com\/#organization"},"articleSection":["Database"],"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 services Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2023-07-27T09:29:50+00:00","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":"Home","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 services Blog","description":"#BeSharing","publisher":{"@id":"https:\/\/www.dbi-services.com\/#organization"},"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":"Organization","@id":"https:\/\/www.dbi-services.com\/#organization","name":"dbi services","url":"https:\/\/www.dbi-services.com\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2026\/09\/dbi-services-logo.png","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2026\/09\/dbi-services-logo.png","width":512,"height":512,"caption":"dbi services"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/dbiservices\/","https:\/\/x.com\/dbiservices","https:\/\/www.linkedin.com\/company\/dbi-services\/","https:\/\/www.youtube.com\/user\/dbiservices"],"description":"Swiss IT consulting and services company specialized in efficient data infrastructures and platforms: databases, application servers, cloud, DevOps, digitalization, analytics, data quality and monitoring. Headquartered in Del\u00e9mont with offices in Nyon, Basel, Bern and Zurich.","email":"info@dbi-services.com","telephone":"+41 32 422 96 00","legalName":"dbi services SA"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0b4548362cac3eeff8b3eaf329e3530a","name":"Open source Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/05\/open-source-author.png","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/05\/open-source-author.png","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/05\/open-source-author.png","caption":"Open source Team"},"description":"Posts published under the Open source Team account are collective contributions by the dbi services open infrastructure consultants. The practice covers open-source databases and platforms, PostgreSQL first of all, along with MySQL, MariaDB, and Linux, from architecture, replication, and high availability to performance tuning and operations under service level agreements, and contributes actively to the PostgreSQL community.","url":"https:\/\/www.dbi-services.com\/blog\/author\/open-source-team\/"}]}},"_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\/28"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=26983"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/26983\/revisions"}],"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"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}