Re: Switching identity column to serial

> On 04/02/2023 01:54 CET Erik Wienhold <ewie(at)ewie(dot)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