Обсуждение: 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
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 <spam_eater@gmx.net>
Sent: Tuesday, December 10, 2019 8:42 AM
To: pgsql-general@lists.postgresql.org
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
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?
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@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? 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.klaver@aklaver.com
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
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