Skip to content

Instantly share code, notes, and snippets.

@stbuehler
Last active May 22, 2024 16:07
Show Gist options
  • Save stbuehler/c63f0d09e05ea74a2cd77cf180b01eb7 to your computer and use it in GitHub Desktop.
Save stbuehler/c63f0d09e05ea74a2cd77cf180b01eb7 to your computer and use it in GitHub Desktop.
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment