The following bug has been logged on the website:
Bug reference: 16969
Logged by: Nate B
Email address: n5jgvx4@nate.sh
PostgreSQL version: 13.2
Operating system: Debian 10
Description:
-- create table with primary key set to be "generated always as identity"
CREATE TABLE test(
id int NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
name text NOT NULL
);
-- insert one or more rows with "id" column omitted;
-- result: success, as expected.
INSERT INTO test (name) VALUES ('Adam');
INSERT INTO test (name) VALUES ('Adam'), ('Eve');
-- insert a single row with "id" set to DEFAULT;
-- result: success, as expected and documented.
INSERT INTO test (id, name) VALUES (DEFAULT, 'Adam');
-- insert more than one row with "id" set to DEFAULT;
-- result: unexpected and undocumented failure with the following error:
-- ERROR: cannot insert into column "id"
-- DETAIL: Column "id" is an identity column defined as GENERATED
ALWAYS.
-- HINT: Use OVERRIDING SYSTEM VALUE to override.
INSERT INTO test (id, name) VALUES (DEFAULT, 'Adam'), (DEFAULT, 'Eve');
-- based on error's hint, this gives success, but should not be necessary:
INSERT INTO test (id, name) OVERRIDING SYSTEM VALUE
VALUES (DEFAULT, 'Adam'), (DEFAULT, 'Eve');
tldr; It is documented that explicitly listing a GENERATED ALWAYS AS
IDENTITY
column during INSERT is permitted, so long as the value given is DEFAULT.
This makes perfect sense, and works if an INSERT statement contains a
single
row as part of the VALUES clause; however, attempting to insert multiple
rows
in the VALUES clause of a single INSERT statement falls apart
unexpectedly.
It's easy to say "don't list the column on INSERT at all", but
unfortunately
some database libraries don't respect this. The one I'm using is a better
library than most, but it does batches of INSERTS for such a column by
listing the primary key explicitly and then submitting the DEFAULT value.
This is perfectly valid according to the postgres docs, and works when
inserting a single row, but errors out with multiple rows.
–Nate