From: | Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk> |
---|---|
To: | pgsql-bugs(at)lists(dot)postgresql(dot)org, fredrik(dot)widlert(at)digpro(dot)se |
Subject: | Re: BUG #15579: Adding a column with default from configuration parameter fails on 11.1 |
Date: | 2019-01-07 19:53:21 |
Message-ID: | 87a7kcqmls.fsf@news-spur.riddles.org.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
>>>>> "PG" == PG Bug reporting form <noreply(at)postgresql(dot)org> writes:
PG> However, creating the table first and then adding the column does
PG> not work on 11.1. It used to work at least from version 9.3 to 10.
PG> create table t (x int);
PG> alter table t add c varchar(50) default
PG> current_setting('public.some_setting');
This used to work ONLY if the table is empty, since the alter table
would evaluate the expression once per row (and hence not evaluate it if
there are no rows).
On PG 11, the new fast-default stuff will evaluate the default once, if
it's not volatile, even if the table is empty. So this is an intended
change.
If you know that the table is empty when you do the alter table, you can
do this, which works on any pg version:
alter table t add c varchar(50),
alter column c set default current_setting('public.some_setting');
(if the table is not empty, then existing rows will get a null value in
column "c")
--
Andrew (irc:RhodiumToad)
From | Date | Subject | |
---|---|---|---|
Next Message | Thomas Munro | 2019-01-07 19:59:18 | Re: BUG #15577: Query returns different results when executed multiple times |
Previous Message | PG Bug reporting form | 2019-01-07 19:40:45 | BUG #15580: ALTER TABLE with new column and ADD PRIMARY KEY throws spurious "column contains null values" |