Re: ALTER TABLE with multiple SET NOT NULL

Allison Kaptur <allison(dot)kaptur(at)gmail(dot)com> writes:
> I encountered a surprising error when writing a migration that both added a
> primary key to a table and added a new NOT NULL column. It threw the error "
> column "col_d" contains null values", even though I supplied a default. The
> migration looks like this:
> CREATE TABLE new_table AS SELECT col_a, col_b, col_c from existing_table;
> ALTER TABLE new_table
> ADD COLUMN col_d UUID UNIQUE NOT NULL DEFAULT uuid_generate_v4(),
> ADD PRIMARY KEY (col_a, col_b, col_c);

Hm, this can be made a good deal more self-contained:

regression=# create table t1 (a int);
CREATE TABLE
regression=# insert into t1 values(1);
INSERT 0 1
regression=# alter table t1 add column b float8 not null default random(),
add primary key(a);
ERROR: column "b" contains null values

It fails like that as far back as I tried (8.4). I'm guessing that we're
doing the ALTER steps in the wrong order, but haven't looked closer than
that.

Interestingly, in v11 and HEAD it works if you use a constant default,
suggesting that the fast-default feature is at least adjacent to the
problem.

regards, tom lane