From: | Ryan Vinzent <ryan(dot)vinzent(at)invitae(dot)com> |
---|---|
To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Subject: | Bug: Cannot insert multiple records using DEFAULT keyword for generated column |
Date: | 2021-02-24 20:18:26 |
Message-ID: | CAJJLo9Wt2Xd06xeuiUH5YWg145Eu2iFAj1Q4YYsa-TO16RBsKQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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!
From | Date | Subject | |
---|---|---|---|
Next Message | PG Bug reporting form | 2021-02-24 21:38:53 | BUG #16896: pg_standby: Missing reference when building on Windows |
Previous Message | Tom Lane | 2021-02-24 15:19:23 | Re: BUG #16801: Invalid memory access on WITH RECURSIVE with nested WITHs |