Migrate from serial to identity

Question

We have a table in a Postgres 14 DB which id was created using SERIAL and we want to migrate to using IDENTITY instead. That table already has a lot of entries in prod. How can we safely migrate to IDENTITY?

I did some research and found a script that might help, but after testing it, the results were not as I expected...

INSERT INTO public.another_table (<columns>) VALUES (<values>);

ALTER TABLE public.another_table
    ALTER COLUMN id DROP DEFAULT;

DROP SEQUENCE public.another_table_id_seq;

ALTER TABLE public.another_table
    ALTER COLUMN id SET DATA TYPE INT;

ALTER TABLE public.another_table
    ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY;

INSERT INTO public.another_table (<columns>) VALUES (<values>);

If you do something like that, postgres will complain with the following message: ERROR: duplicate key value violates unique constraint.

This `ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY` is going to add a sequence that starts at 1 which means you will repeating `id`'s when you add new items to the table. You will need to modify the underlying sequence to have its `setval` to be the `max(id)` from the original data. So `select max(id) from public.another_table` then take that number and `select setval(pg_get_serial_sequence('public.another_table'), )`. Then you can do new inserts. — Adrian Klaver, May 26 '23 at 22:21

score 0 · Answer 1 · answered May 28 '23 at 20:02

You can use an anonymous block to generate the necessary SQL for this. Within that block get the nevtval from the current sequence and use that (plus some increment) as the start with value for the new generation sequence_option. Then drop the sequence after the switch. See demo here.

do $$
declare 
  l_gen_start text = format('alter table users alter column user_id add generated by default as identity (start with %s);'
                           ,nextval('<current sequence name>')+100  -- some value above current 
                           );                       
begin
  alter table users alter column user_id drop default;
  raise notice '%', l_gen_start; 
  execute l_gen_start;
  drop sequence users_user_id_seq;
  commit; 
end; 
$$;

Migrate from serial to identity

1 Answers1