RE: Identity columns, DEFAULT keyword and multi-row inserts

Hi Thomas,

I agree that it does not seem very consistent.
But is there any specific reason why are you using DEFAULT ?
Why don't you simply execute :
insert into test (data)
values
(1),
(2);

If you want / have to specify DEFAULT, then you should probably create your identity as "generated by default".

Regards,

Patrick Fiche
Database Engineer, Aqsacom Sas.
c. 33 6 82 80 69 96

-----Original Message-----
From: Thomas Kellerer <spam_eater(at)gmx(dot)net>
Sent: Tuesday, December 10, 2019 8:42 AM
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Identity columns, DEFAULT keyword and multi-row inserts

Hello,

assume the following table:

create table test
(
id integer not null generated always as identity,
data integer not null
);

The following insert works fine:

insert into test (id, data)
values (default,1);

However, a multi-row insert like the following:

insert into test (id, data)
values
(default,1),
(default,2);

fails with:

ERROR: cannot insert into column "id"
Detail: Column "id" is an identity column defined as GENERATED ALWAYS.
Hint: Use OVERRIDING SYSTEM VALUE to override.

My question is:

* If DEFAULT is not allowed for identity columns, then why does the single-row insert work?
* If DEFAULT _is_ allowed, then why does the multi-row insert fail?

The above happens with Postgres 10,11 and 12

Regards
Thomas