From: Erik Wienhold <ewie@ewie.name>
To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Switching identity column to serial
Date: Sat, 4 Feb 2023 01:54:39 +0100 (CET)
Message-ID: <1828022907.15226.1675472079824@office.mailbox.org> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: ewie@ewie.name
Subject: Re: Switching identity column to serial
In-Reply-To: <1828022907.15226.1675472079824@office.mailbox.org>
References: <1828022907.15226.1675472079824@office.mailbox.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox