Re: PGSQL bug - "Column ??? is an identity column defined as GENERATED ALWAYS.",

> On Aug 19, 2021, at 12:17 AM, Petr Hybler <petr(dot)hybler(at)gmail(dot)com> wrote:
>
> Is there a possibility to have that fixed rather than using the workaround? The problem is with ORM frameworks where there is no possibility to use this clause OVERRIDING SYSTEM VALUE ...
>

Depending on your ORM, it may be possible to alter how it constructs this particular statement. For instance, SQLAlchemy 1.3 has a hook that we use to change the syntax it generates for autoincrement PK columns.

https://docs.sqlalchemy.org/en/13/dialects/postgresql.html#postgresql-10-identity-columns

>
> On Wed, Aug 18, 2021 at 11:54 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)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