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

Patrick FICHE schrieb am 10.12.2019 um 08:56:
>> -----Original Message-----
>> From: Thomas Kellerer <spam_eater(at)gmx(dot)net>
>>
>> assume the following table:
>>
>> create table test
>> (
>> id integer not null generated always as identity,
>> data integer not null
>> );
>>
>> 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?
>
>
> I agree that it does not seem very consistent.
>
> But is there any specific reason why are you using DEFAULT ?
>
> If you want / have to specify DEFAULT, then you should probably
> create your identity as "generated by default".
I don't really need (or use) it, I just stumbled upon this: https://stackoverflow.com/questions/59261048

And I think if the single row insert is allowed the multi-row should be as well.

Not sure if this is a bug - and if it is, which one is the bug: the failing statement or the working one?