postgres: upgrade serial nextval columnes to identity (https://www.2ndquadrant.com/en/blog/postgresql-10-identity-columns/, https://stackoverflow.com/questions/59232753/how-to-change-a-table-id-from-serial-to-identity/59233169#59233169)

upgrade_serial_to_identity.sql

CREATE OR REPLACE PROCEDURE upgrade_serial_to_identity(tbl regclass, col name)
LANGUAGE plpgsql
AS $$
DECLARE
  colnum smallint;
  count int;
  maxval int;
  cmd text;
  r RECORD;
BEGIN
  -- find column number
  SELECT attnum INTO colnum FROM pg_attribute WHERE attrelid = tbl AND attname = col;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'column does not exist';
  END IF;

  SELECT INTO count COUNT(*)
    FROM pg_depend
    WHERE (refclassid, refobjid, refobjsubid) = ('pg_class'::regclass, tbl, colnum)
      AND classid = 'pg_class'::regclass AND objsubid = 0
      AND deptype = 'a';

  IF count = 0 THEN
    RAISE EXCEPTION 'no linked sequence found for column %.%', tbl, col;
  END IF;

  cmd := format('ALTER TABLE %s ALTER COLUMN %I DROP DEFAULT;', tbl, col);

  RAISE NOTICE 'EXECUTE: %', cmd;
  EXECUTE cmd;

  -- find linked sequences (for whatever reasons there sometimes are more than one)
  FOR r IN SELECT objid::regclass
    FROM pg_depend
    WHERE (refclassid, refobjid, refobjsubid) = ('pg_class'::regclass, tbl, colnum)
      AND classid = 'pg_class'::regclass AND objsubid = 0
      AND deptype = 'a'
  LOOP
    cmd := format('DROP SEQUENCE %s;', r.objid);
    RAISE NOTICE 'EXECUTE: % -- last value: %', cmd, pg_sequence_last_value(r.objid);
    EXECUTE cmd;
  END LOOP;

  EXECUTE 'SELECT MAX(' || quote_ident(col) || ') FROM ' || tbl INTO maxval;
  RAISE NOTICE '-- current max(%.%) value: %', tbl, quote_ident(col), maxval;

  cmd := format('ALTER TABLE %s ALTER %I ADD GENERATED BY DEFAULT AS IDENTITY', tbl, col);
  IF maxval IS NULL THEN
    cmd := cmd || ';';
  ELSE
    cmd := cmd || format(' (RESTART %s);', maxval + 1);
  END IF;
  RAISE NOTICE 'EXECUTE: %', cmd;
  EXECUTE cmd;
END;
$$;

CREATE OR REPLACE PROCEDURE upgrade_all_serial_to_identity()
LANGUAGE plpgsql
AS $$
DECLARE
  r RECORD;
BEGIN
  FOR r in SELECT a.attrelid::regclass AS tbl, a.attname::name AS col
    FROM
    pg_attribute a
    LEFT JOIN pg_attrdef ad ON a.attrelid = ad.adrelid AND a.attnum = ad.adnum
    WHERE a.attgenerated = ''
    AND pg_get_expr(ad.adbin, ad.adrelid) LIKE 'nextval(%'
  LOOP
    CALL upgrade_serial_to_identity(r.tbl, r.col);
  END LOOP;
END;
$$;

BEGIN;
CALL upgrade_all_serial_to_identity();
-- COMMIT;
-- ROLLBACK;

DROP PROCEDURE upgrade_all_serial_to_identity;
DROP PROCEDURE upgrade_serial_to_identity;