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

> On Aug 19, 2021, at 12:17 AM, Petr Hybler <petr.hybler@gmail.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@sss.pgh.pa.us> wrote:
> "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