PostgreSQL 10 Identity Column gets "null value" when inserting multiple rows with default keyword 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 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?
This is in fact a bug. I verified it. I went to go see if it was filed and it seems it already is. It's not just filed, the commit is there.

You can see their test, exactly like yours 

+-- VALUES RTEs
+INSERT INTO itest3 VALUES (DEFAULT, 'a');
+INSERT INTO itest3 VALUES (DEFAULT, 'b'), (DEFAULT, 'c');
+SELECT * FROM itest3;


So just wait, it's there for PostgreSQL 10.2.

Possible work around for PostgreSQL &lt; 10.2

If you absolutely must have this, and using the implicit column isn't acceptable. One easy solution would be to retrieve the sequence with the catalog info function

pg_get_serial_sequence(table_name, column_name) 


Which I believe should work, and to set that as a default.

ALTER TABLE ONLY test
  ALTER COLUMN id
  DEFAULT nextval('seqname');