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@gmx.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?