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

I am using Sequelize - but I figured a way with bulkCreate there is an
option to list fields I wanna use in the query, so I list the entire table
down except the PK (Generated Identity column) ... anyway, thx for the
response guys, much appreciated

On Thu, Aug 19, 2021 at 2:43 PM Philip Semanchuk <
philip@americanefficient.com> wrote:

>
>
> > 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
>
>