I recently upgraded from Postgres 9.5 to Postgres 10. One of the nifty features in Postgres 10 is the new identity column type, an alternative to Postgres’ serial pseudo-type. Official documentation for identity column can be found one the CREATE TABLE
page.
However, when inserting multiple rows into a table with a GENERATED BY DEFAULT AS IDENTITY
column and using the keyword DEFAULT
to get the next ID value, the default value is coming back as null
.
For example, let’s say I have a table
CREATE TABLE test ( id int GENERATED BY DEFAULT AS IDENTITY, t text ); CREATE TABLE
Inserting a single row with the DEFAULT
keyword seems to work fine.
INSERT INTO test (id, t) VALUES (DEFAULT, 'a'); INSERT 0 1
Inserting multiple rows does not.
INSERT INTO test (id, t) VALUES (DEFAULT, 'b'), (DEFAULT, 'c'); ERROR: null value in column "id" violates not-null constraint DETAIL: Failing row contains (null, b).
Inserting multiple rows using an implicit default also works.
INSERT INTO test (t) VALUES ('d'), ('e'); INSERT 0 2
The problem specified above does not appear to be present when using the SERIAL
column pseudo-type.
CREATE TABLE test2 ( id SERIAL, t text ); CREATE TABLE INSERT INTO test2 (id, t) VALUES (DEFAULT, 'a'), (DEFAULT, 'b'); INSERT 0 2
So my question is: am I missing something? Is the DEFAULT
keyword just not expected to work with the new identity column? Or is this a bug?