Bug: Cannot insert multiple records using DEFAULT keyword for generated column

Started by Ryan Vinzentabout 5 years ago3 messagesbugs
Jump to latest
#1Ryan Vinzent
ryan.vinzent@invitae.com

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!

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Ryan Vinzent (#1)
Re: Bug: Cannot insert multiple records using DEFAULT keyword for generated column

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&amp;a=commitdiff&amp;h=17958972f

but there hasn't been any discussion of back-patching that.

regards, tom lane

#3Ryan Vinzent
ryan.vinzent@invitae.com
In reply to: Tom Lane (#2)
Re: Bug: Cannot insert multiple records using DEFAULT keyword for generated column

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:

Show quoted text

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&amp;a=commitdiff&amp;h=17958972f

but there hasn't been any discussion of back-patching that.

regards, tom lane