Re: Switching identity column to serial

Ron <ronljohnsonjr@xxxxxxxxx> · Fri, 3 Feb 2023 22:41:06 -0600



    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.