PostgreSQL 10 – Identity Column Gets ‘Null Value’ When Inserting Multiple Rows with Default Keyword

identitypostgresqlpostgresql-10

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?

Best Answer

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 < 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');
Related Question