From: Tom Lane <tgl@sss.pgh.pa.us>
To: David G. Johnston <david.g.johnston@gmail.com>
Cc: Peter Eisentraut <peter.eisentraut@enterprisedb.com>
Cc: Petr Hybler <petr.hybler@gmail.com>
Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: Re: PGSQL bug - "Column ??? is an identity column defined as GENERATED ALWAYS.",
Date: Wed, 18 Aug 2021 17:54:36 -0400
Message-ID: <1621287.1629323676@sss.pgh.pa.us> (raw)
In-Reply-To: <CAKFQuwaGiwrCLEy8C7rkjw8PDCVnDRuc63Fjrotv14eQ9whnpg@mail.gmail.com>
References: <CAEMmGb5BN_iRabOKO1_G6LkecxqLHrwFQ=cuN1bJC4HmUsjjLw@mail.gmail.com>
<949dd35d-596e-efce-c5fb-bfef19b74d3b@enterprisedb.com>
<CAKFQuwaGiwrCLEy8C7rkjw8PDCVnDRuc63Fjrotv14eQ9whnpg@mail.gmail.com>
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Wednesday, August 18, 2021, Peter Eisentraut <peter.eisentraut@
> enterprisedb.com> wrote:
>> This has been fixed in PostgreSQL 14.
> The OP is reporting a regression, saying it is fixed in v14 isn’t a useful
> response. Is it also fixed in v11.14?
The OP would have to provide some evidence that there's actually any
regression. AFAIK that code was like that since IDENTITY columns were
introduced. v14 does improve matters, but we judged the fix too invasive
to risk back-patching.
BTW, the v11 error message points out a simple workaround, which
seems to do the trick:
regression=# CREATE TABLE sample_table (
id int8 NOT NULL GENERATED ALWAYS AS IDENTITY,
name varchar(255) NOT NULL,
description text NOT NULL,
CONSTRAINT sample_table_pk PRIMARY KEY (id)
);
CREATE TABLE
regression=# INSERT INTO sample_table (id, name, description)VALUES (DEFAULT, 'John
Doe', 'Test description')
, (DEFAULT, 'Jane Eod', 'Not working');
ERROR: cannot insert into column "id"
DETAIL: Column "id" is an identity column defined as GENERATED ALWAYS.
HINT: Use OVERRIDING SYSTEM VALUE to override.
regression=# INSERT INTO sample_table (id, name, description) OVERRIDING SYSTEM VALUE VALUES (DEFAULT, 'John
Doe', 'Test description')
, (DEFAULT, 'Jane Eod', 'Not working');
INSERT 0 2
regression=# table sample_table;
id | name | description
----+----------+------------------
1 | John +| Test description
| Doe |
2 | Jane Eod | Not working
(2 rows)
Yeah, per spec you shouldn't have to say OVERRIDING SYSTEM VALUE
for this case, but it didn't seem worth the risk of back-patching
to improve that in stable branches.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, david.g.johnston@gmail.com, peter.eisentraut@enterprisedb.com, petr.hybler@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: PGSQL bug - "Column ??? is an identity column defined as GENERATED ALWAYS.",
In-Reply-To: <1621287.1629323676@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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