Re: Identity columns, DEFAULT keyword and multi-row inserts - Mailing list pgsql-general

Thomas Kellerer <spam_eater@gmx.net> writes:
> 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.

Yeah, in principle a multi-row INSERT could grovel through all the
rows of the VALUES clause and confirm that every one of them has
DEFAULT there.  Not sure it's worth the trouble, but if it's not
a lot of code then maybe.  It certainly seems a bit inconsistent.

            regards, tom lane