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.

--
Born in Arizona, moved to Babylonia.