Re: Switching identity column to serial - Mailing list pgsql-general

> On 04/02/2023 01:54 CET Erik Wienhold <ewie@ewie.name> wrote:
>
> I was wondering if it's possible to drop a column identity (not the column
> itself) while keeping the attached sequence.  This would avoid recreating
> an identical sequence (especially with a correct start value and owner).
>
> Changing the sequence owner to NONE before dropping identity is not allowed.
> Also changing pg_class.relowner to some role did not help.  The sequence is
> still dropped together with the column identity.
>
> But I managed it by clearing pg_attribute.attidentity.  See the following
> psql session:

Forgot to mention: tested on 12.13 and 15.1.

>
>     test=# create table t (id int generated always as identity, x char);
>     CREATE TABLE
>
>     test=# insert into t (x) values ('a'), ('b') returning *;
>      id | x
>     ----+---
>       1 | a
>       2 | b
>     (2 rows)
>
>     INSERT 0 2
>
>     test=# select pg_get_serial_sequence('t', 'id');
>      pg_get_serial_sequence
>     ------------------------
>      public.t_id_seq
>     (1 row)
>
>     test=# update pg_attribute set attidentity = '' where (attrelid, attname) = ('t'::regclass, 'id');
>     UPDATE 1
>
>     test=# alter table t alter id drop identity;
>     psql:1.sql:6: ERROR:  column "id" of relation "t" is not an identity column
>
>     test=# select pg_get_serial_sequence('t', 'id');
>      pg_get_serial_sequence
>     ------------------------
>      public.t_id_seq
>     (1 row)
>
>     test=# alter table t alter id set default nextval('t_id_seq');
>     ALTER TABLE
>
>     test=# insert into t (x) values ('c'), ('d') returning *;
>      id | x
>     ----+---
>       3 | c
>       4 | d
>     (2 rows)
>
>     INSERT 0 2
>
>     test=# insert into t (id, x) values (-1, 'e') returning *;
>      id | x
>     ----+---
>      -1 | e
>     (1 row)
>
>     INSERT 0 1
>
>     test=# select * from t;
>      id | x
>     ----+---
>       1 | a
>       2 | b
>       3 | c
>       4 | d
>      -1 | e
>     (5 rows)
>
> Is this sufficient or am I missing some detail and messing around with
> pg_catalog is not enough (in addition to being risky)?
>
> Some context:
>
> I have to change identity columns to a form that resembles a definition as
> serial.  Creating a new column and migrating the primary key constraint is
> not an option.
>
> Why is this change necessary?
>
> My team is importing data with QGIS which fails to properly handle identity
> columns.  QGIS uses INSERT with OVERRIDING SYSTEM VALUE but tries to insert
> NULL although identity columns imply NOT NULL (also it's the primary key).
> QGIS tries to generate an ID with nextval but does not use the qualified
> sequence name although search_path does not contain the namespace.  It's
> weird that QGIS thinks that it should generate the ID instead of delegating
> this to the database, yet it uses RETURNING id.  Maybe it needs the ID in
> advance for reference.  I don't know.
>
> The "serial" style with nextval as column default works as expected.
> Probably because QGIS just uses the column default expression which should
> reference the correct sequence.  Oh, did I mention yet that QGIS generates
> the ID before issuing an INSERT with RETURNING id?
>
> I'll still open a bug ticket with QGIS but right now there's no other way
> than ditching identity columns.
>
> --
> Erik