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?