{"id":11439,"date":"2018-07-13T06:32:24","date_gmt":"2018-07-13T04:32:24","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/pgday-amsterdam-follow-up-1-adding-columns-with-a-default-value-and-changing-the-default-value-right-after\/"},"modified":"2018-07-13T06:32:24","modified_gmt":"2018-07-13T04:32:24","slug":"pgday-amsterdam-follow-up-1-adding-columns-with-a-default-value-and-changing-the-default-value-right-after","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/pgday-amsterdam-follow-up-1-adding-columns-with-a-default-value-and-changing-the-default-value-right-after\/","title":{"rendered":"PGDay Amsterdam &#8211; follow up 1 &#8211; Adding columns with a default value and changing the default value right after"},"content":{"rendered":"<p>As always, this time during my talk about the PostgreSQL 11 new features in Amsterdam, there have been question I could not immediately answer. The first one was this: Suppose we add a column with a default value in PostgreSQL 11, what happens when we change that default afterwards? Does the table get rewritten? Do we have more than on distinct default value for that column? Here we go &#8230; <\/p>\n<p><!--more--><\/p>\n<p>The sample table:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# select version();\n                                                            version                                                            \n-------------------------------------------------------------------------------------------------------------------------------\n PostgreSQL 11beta1 build on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit\n(1 row)\npostgres=# create table t1 ( a int, b text );\nCREATE TABLE\npostgres=# insert into t1 (a,b) \n           select a.*, md5(a::text) \n             from generate_series(1,1000) a;\nINSERT 0 1000\n<\/pre>\n<p>Lets add a new column with a default value:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# alter table t1 add column c text default 'aa';;\nALTER TABLE\n<\/pre>\n<p>This populates the two columns in pg_attribute as described in a <a href=\"https:\/\/www.dbi-services.com\/blog\/postgresql-11-instant-add-column-with-a-non-null-default-value\/\" target=\"_blank\" rel=\"noopener\">previous post<\/a>:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# select atthasmissing,attmissingval \n             from pg_attribute \n            where attrelid = 't1'::regclass and attname = 'c';\n atthasmissing | attmissingval \n---------------+---------------\n t             | {aa}\n(1 row)\n<\/pre>\n<p>When we check for the distinct values in column &#8220;c&#8221; we should only see one result (which is &#8220;aa&#8221;):<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# select c, count(*) from t1 group by c;\n c  | count \n----+-------\n aa |  1000\n(1 row)\n<\/pre>\n<p>When I got the question right the concern was: When we change the default now do we see two results when we ask for the distinct values in column &#8220;c&#8221;? Of course not and the table is not rewritten:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# alter table t1 alter column c set default 'bb';\nALTER TABLE\npostgres=# select c, count(*) from t1 group by c;\n c  | count \n----+-------\n aa |  1000\n(1 row)\n\npostgres=# select atthasmissing,attmissingval from pg_attribute where attrelid = 't1'::regclass and attname = 'c';\n atthasmissing | attmissingval \n---------------+---------------\n t             | {aa}\n(1 row)\n<\/pre>\n<p>What does that mean? For the existing rows the value is still &#8220;aa&#8221; as that was true when the column was added. For new values we will get &#8220;bb&#8221;:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# d t1\n                  Table \"public.t1\"\n Column |  Type   | Collation | Nullable |  Default   \n--------+---------+-----------+----------+------------\n a      | integer |           |          | \n b      | text    |           |          | \n c      | text    |           |          | 'bb'::text\n\npostgres=# insert into t1 (a,b) values (1001,'aa');\nINSERT 0 1\npostgres=# select c, count(*) from t1 group by c;\n c  | count \n----+-------\n bb |     1\n aa |  1000\n(2 rows)\n<\/pre>\n<p>I hope that answers the question. If not, please leave a comment.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>As always, this time during my talk about the PostgreSQL 11 new features in Amsterdam, there have been question I could not immediately answer. The first one was this: Suppose we add a column with a default value in PostgreSQL 11, what happens when we change that default afterwards? Does the table get rewritten? Do [&hellip;]<\/p>\n","protected":false},"author":28,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[77],"type_dbi":[],"class_list":["post-11439","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-postgresql"],"acf":[],"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>PGDay Amsterdam - follow up 1 - Adding columns with a default value and changing the default value right after - 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\/pgday-amsterdam-follow-up-1-adding-columns-with-a-default-value-and-changing-the-default-value-right-after\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"PGDay Amsterdam - follow up 1 - Adding columns with a default value and changing the default value right after\" \/>\n<meta property=\"og:description\" content=\"As always, this time during my talk about the PostgreSQL 11 new features in Amsterdam, there have been question I could not immediately answer. The first one was this: Suppose we add a column with a default value in PostgreSQL 11, what happens when we change that default afterwards? Does the table get rewritten? Do [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/pgday-amsterdam-follow-up-1-adding-columns-with-a-default-value-and-changing-the-default-value-right-after\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2018-07-13T04:32:24+00:00\" \/>\n<meta name=\"author\" content=\"Open source Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\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\\\/pgday-amsterdam-follow-up-1-adding-columns-with-a-default-value-and-changing-the-default-value-right-after\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/pgday-amsterdam-follow-up-1-adding-columns-with-a-default-value-and-changing-the-default-value-right-after\\\/\"},\"author\":{\"name\":\"Open source Team\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/4bae5e5dd8c7d48edbd948772b49b202\"},\"headline\":\"PGDay Amsterdam &#8211; follow up 1 &#8211; Adding columns with a default value and changing the default value right after\",\"datePublished\":\"2018-07-13T04:32:24+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/pgday-amsterdam-follow-up-1-adding-columns-with-a-default-value-and-changing-the-default-value-right-after\\\/\"},\"wordCount\":211,\"commentCount\":0,\"keywords\":[\"PostgreSQL\"],\"articleSection\":[\"Database Administration &amp; Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/pgday-amsterdam-follow-up-1-adding-columns-with-a-default-value-and-changing-the-default-value-right-after\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/pgday-amsterdam-follow-up-1-adding-columns-with-a-default-value-and-changing-the-default-value-right-after\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/pgday-amsterdam-follow-up-1-adding-columns-with-a-default-value-and-changing-the-default-value-right-after\\\/\",\"name\":\"PGDay Amsterdam - follow up 1 - Adding columns with a default value and changing the default value right after - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2018-07-13T04:32:24+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/4bae5e5dd8c7d48edbd948772b49b202\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/pgday-amsterdam-follow-up-1-adding-columns-with-a-default-value-and-changing-the-default-value-right-after\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/pgday-amsterdam-follow-up-1-adding-columns-with-a-default-value-and-changing-the-default-value-right-after\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/pgday-amsterdam-follow-up-1-adding-columns-with-a-default-value-and-changing-the-default-value-right-after\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"PGDay Amsterdam &#8211; follow up 1 &#8211; Adding columns with a default value and changing the default value right after\"}]},{\"@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\\\/4bae5e5dd8c7d48edbd948772b49b202\",\"name\":\"Open source Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"caption\":\"Open source Team\"},\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/open-source-team\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"PGDay Amsterdam - follow up 1 - Adding columns with a default value and changing the default value right after - 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\/pgday-amsterdam-follow-up-1-adding-columns-with-a-default-value-and-changing-the-default-value-right-after\/","og_locale":"en_US","og_type":"article","og_title":"PGDay Amsterdam - follow up 1 - Adding columns with a default value and changing the default value right after","og_description":"As always, this time during my talk about the PostgreSQL 11 new features in Amsterdam, there have been question I could not immediately answer. The first one was this: Suppose we add a column with a default value in PostgreSQL 11, what happens when we change that default afterwards? Does the table get rewritten? Do [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/pgday-amsterdam-follow-up-1-adding-columns-with-a-default-value-and-changing-the-default-value-right-after\/","og_site_name":"dbi Blog","article_published_time":"2018-07-13T04:32:24+00:00","author":"Open source Team","twitter_card":"summary_large_image","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\/pgday-amsterdam-follow-up-1-adding-columns-with-a-default-value-and-changing-the-default-value-right-after\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/pgday-amsterdam-follow-up-1-adding-columns-with-a-default-value-and-changing-the-default-value-right-after\/"},"author":{"name":"Open source Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/4bae5e5dd8c7d48edbd948772b49b202"},"headline":"PGDay Amsterdam &#8211; follow up 1 &#8211; Adding columns with a default value and changing the default value right after","datePublished":"2018-07-13T04:32:24+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/pgday-amsterdam-follow-up-1-adding-columns-with-a-default-value-and-changing-the-default-value-right-after\/"},"wordCount":211,"commentCount":0,"keywords":["PostgreSQL"],"articleSection":["Database Administration &amp; Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/pgday-amsterdam-follow-up-1-adding-columns-with-a-default-value-and-changing-the-default-value-right-after\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/pgday-amsterdam-follow-up-1-adding-columns-with-a-default-value-and-changing-the-default-value-right-after\/","url":"https:\/\/www.dbi-services.com\/blog\/pgday-amsterdam-follow-up-1-adding-columns-with-a-default-value-and-changing-the-default-value-right-after\/","name":"PGDay Amsterdam - follow up 1 - Adding columns with a default value and changing the default value right after - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2018-07-13T04:32:24+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/4bae5e5dd8c7d48edbd948772b49b202"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/pgday-amsterdam-follow-up-1-adding-columns-with-a-default-value-and-changing-the-default-value-right-after\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/pgday-amsterdam-follow-up-1-adding-columns-with-a-default-value-and-changing-the-default-value-right-after\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/pgday-amsterdam-follow-up-1-adding-columns-with-a-default-value-and-changing-the-default-value-right-after\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"PGDay Amsterdam &#8211; follow up 1 &#8211; Adding columns with a default value and changing the default value right after"}]},{"@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\/4bae5e5dd8c7d48edbd948772b49b202","name":"Open source Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","caption":"Open source Team"},"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\/11439","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=11439"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/11439\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=11439"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=11439"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=11439"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=11439"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}