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

On 12/10/19 12:15 AM, Thomas Kellerer wrote:
> 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?

I would say the failing one:

https://www.postgresql.org/docs/12/sql-insert.html

"OVERRIDING SYSTEM VALUE

Without this clause, it is an error to specify an explicit value
(other than DEFAULT) for an identity column defined as GENERATED ALWAYS.
This clause overrides that restriction.
"

>
>
>
>
>
>
>
>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com