Re: Switching identity column to serial

On 2/3/23 18:54, Erik Wienhold 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).

Why doesn't this work?
BEGIN;
DROP SEQUENCE t_id;
CREATE SEQUENCE new_t_id_seq AS INTEGER OWNED BY t.id;
ALTER SEQUENCE new_t_id_seq OWNER TO new_owner;
SELECT setval('new_t_id', (SELECT MAX(id) FROM t));
SELECT nextval('new_t_id');
COMMIT;

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.

Manually diigging around the system catalog is never recommended.