pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Bug: 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>
  2021-02-24 22:05 ` Re: Bug: Cannot insert multiple records using DEFAULT keyword for generated column Tom Lane <tgl@sss.pgh.pa.us>
  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 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   ` Re: Bug: Cannot insert multiple records using DEFAULT keyword for generated column 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-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 ` Re: Bug: Cannot insert multiple records using DEFAULT keyword for generated column Tom Lane <tgl@sss.pgh.pa.us>
@ 2021-02-25 00:39   ` Ryan Vinzent <ryan.vinzent@invitae.com>
  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