I recently upgraded from PostgreSQL 9.5 to PostgreSQL 10. One of the nifty features in PostgreSQL 10 is the new identity column type, an alternative to PostgreSQL' 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
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?