{"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":29,"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 v27.2 (Yoast SEO v27.2) - 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=\"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\/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\":\"Daniel Westermann\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"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\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"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\/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":"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":"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\/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":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"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\/8d08e9bd996a89bd75c0286cbabf3c66"},"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\/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\/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\/29"}],"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}]}}