Identity columns, DEFAULT keyword and multi-row inserts

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Identity columns, DEFAULT keyword and multi-row inserts

Thomas Kellerer
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


Reply | Threaded
Open this post in threaded view
|

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

pafiti
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 <[hidden email]>
Sent: Tuesday, December 10, 2019 8:42 AM
To: [hidden email]
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


Reply | Threaded
Open this post in threaded view
|

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

Thomas Kellerer
Patrick FICHE schrieb am 10.12.2019 um 08:56:

>> -----Original Message-----
>> From: Thomas Kellerer <[hidden email]>
>>
>> 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?




 
 



Reply | Threaded
Open this post in threaded view
|

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

Adrian Klaver-4
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 <[hidden email]>
>>>
>>> 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
[hidden email]


Reply | Threaded
Open this post in threaded view
|

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

Tom Lane-2
In reply to this post by Thomas Kellerer
Thomas Kellerer <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

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

Peter Eisentraut-6
On 2019-12-10 17:53, Tom Lane wrote:

>> 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.

It looks like the multi-row case in transformInsertStmt() would have to
develop a bit more smarts to discover this case and then replace the RTE
reference in the target list with a single SetToDefault node?

--
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services