pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBug: Cannot insert multiple records using DEFAULT keyword for generated column
3+ messages / 2 participants
[nested] [flat]
* Bug: Cannot insert multiple records using DEFAULT keyword for generated column
@ 2021-02-24 20:18 Ryan Vinzent <ryan.vinzent@invitae.com>
0 siblings, 1 reply; 3+ messages in thread
From: Ryan Vinzent @ 2021-02-24 20:18 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
Hi all,
I have run into an issue and I'm fairly sure this can be classified as a bug.
Postgres version (official 13.2 docker image):
PostgreSQL 13.2 (Debian 13.2-1.pgdg100+1)
on x86_64-pc-linux-gnu compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
The documentation on generated columns states:
> A generated column cannot be written to directly. In INSERT or UPDATE commands, a value cannot be specified for a generated column, but the keyword DEFAULT may be specified.
Given this table schema
```
CREATE TABLE "generated_column_table" (
"id" BIGSERIAL NOT NULL PRIMARY KEY,
"name" TEXT NOT NULL,
"id_plus_one" BIGINT NOT NULL GENERATED ALWAYS AS ("id" + 1) STORED
);
```
I can insert single records, e.g. this works as expected:
```
INSERT INTO
"generated_column_table" ("name", "id_plus_one")
VALUES
('some text', DEFAULT);
```
Using the DEFAULT keyword on the regular ID column for multiple
records does not throw an error, as expected:
```
INSERT INTO
"generated_column_table" ("id", "name")
VALUES
(DEFAULT, 'some text'),
(DEFAULT, 'other text');
```
However inserting multiple records using DEFAULT for the generated
column throws an error
```
INSERT INTO
"generated_column_table" ("name", "id_plus_one")
VALUES
('some text', DEFAULT),
('other text', DEFAULT);
```
ERROR: cannot insert into column "id_plus_one"
DETAIL: Column "id_plus_one" is a generated column.
The issue is specific multiple records inserting DEFAULT for generated columns.
I would expect this to insert values successfully, given the
documentation doesn't state it's not allowed. If this is not
considered valid, the documentation could be more explicit on using
the DEFAULT keyword when inserting multiple records.
Thanks! Please let me know if any additional information is required!
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Bug: Cannot insert multiple records using DEFAULT keyword for generated column
@ 2021-02-24 22:05 Tom Lane <tgl@sss.pgh.pa.us>
parent: Ryan Vinzent <ryan.vinzent@invitae.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2021-02-24 22:05 UTC (permalink / raw)
To: Ryan Vinzent <ryan.vinzent@invitae.com>; +Cc: pgsql-bugs@lists.postgresql.org
Ryan Vinzent <ryan.vinzent@invitae.com> writes:
> I have run into an issue and I'm fairly sure this can be classified as a bug.
More an unimplemented feature ...
> INSERT INTO
> "generated_column_table" ("name", "id_plus_one")
> VALUES
> ('some text', DEFAULT),
> ('other text', DEFAULT);
>
> ERROR: cannot insert into column "id_plus_one"
> DETAIL: Column "id_plus_one" is a generated column.
This works in HEAD, as of
https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=17958972f
but there hasn't been any discussion of back-patching that.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Bug: Cannot insert multiple records using DEFAULT keyword for generated column
@ 2021-02-25 00:39 Ryan Vinzent <ryan.vinzent@invitae.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Ryan Vinzent @ 2021-02-25 00:39 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@lists.postgresql.org
Thanks!
If there are no plans to back-patch as a bug, then some added clarity in
documentation of the affected versions would be very helpful. The line
regarding inserts with the DEFAULT keyword gives no mention of
different behavior between multi row inserts and single row inserts, so I
feel it's fair for users to assume both work given the current wording.
Something like this would make it clear.
A generated column cannot be written to directly. In INSERT or UPDATE
> commands, a value cannot be specified for a generated column, but the
> keyword DEFAULT may be specified *for single row inserts only*.
Regards,
Ryan
On Wed, Feb 24, 2021 at 2:05 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Ryan Vinzent <ryan.vinzent@invitae.com> writes:
> > I have run into an issue and I'm fairly sure this can be classified as a
> bug.
>
> More an unimplemented feature ...
>
> > INSERT INTO
> > "generated_column_table" ("name", "id_plus_one")
> > VALUES
> > ('some text', DEFAULT),
> > ('other text', DEFAULT);
> >
> > ERROR: cannot insert into column "id_plus_one"
> > DETAIL: Column "id_plus_one" is a generated column.
>
> This works in HEAD, as of
>
>
> https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=17958972f
>
> but there hasn't been any discussion of back-patching that.
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2021-02-25 00:39 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-02-24 20:18 Bug: Cannot insert multiple records using DEFAULT keyword for generated column Ryan Vinzent <ryan.vinzent@invitae.com>
2021-02-24 22:05 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-02-25 00:39 ` Ryan Vinzent <ryan.vinzent@invitae.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox