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

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!

Responses

Browse pgsql-bugs by date

  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