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