Last active
April 25, 2024 12:26
-
-
Save MarkusH/4c03d0867ba37b6b89379bced12cb3ae to your computer and use it in GitHub Desktop.
A script that outputs the raw SQL to convert Django's serial columns to identity columns.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
WITH tab AS ( | |
SELECT | |
a.attrelid::regclass::text AS t, | |
a.attname AS c, | |
pg_get_serial_sequence(a.attrelid::regclass::text, a.attname) AS s, | |
nextval(pg_get_serial_sequence(a.attrelid::regclass::text, a.attname)) AS v | |
FROM pg_attribute a | |
JOIN pg_class c ON c.oid = a.attrelid | |
WHERE | |
a.attrelid::regclass::text LIKE '%' | |
AND c.relkind IN ('r', 'p') /* regular and partitioned tables */ | |
AND a.attnum > 0 | |
AND NOT a.attisdropped | |
AND a.atttypid = ANY ('{int,int8,int2}'::regtype[]) | |
AND EXISTS ( | |
SELECT FROM pg_attrdef ad | |
WHERE | |
ad.adrelid = a.attrelid | |
AND ad.adnum = a.attnum | |
AND ( | |
pg_get_expr(ad.adbin, ad.adrelid) | |
= | |
'nextval(''' | |
|| ( | |
pg_get_serial_sequence(a.attrelid::regclass::text, a.attname) | |
)::regclass | |
|| '''::regclass)' | |
) | |
) | |
ORDER BY a.attnum | |
) | |
SELECT | |
'BEGIN; ' | |
|| 'LOCK TABLE ' || quote_ident(t) || ' IN ACCESS EXCLUSIVE MODE; ' | |
|| 'ALTER TABLE ' || quote_ident(t) || ' ALTER COLUMN ' || quote_ident(c) || ' DROP DEFAULT; ' | |
|| 'DROP SEQUENCE ' || s || '; ' | |
|| 'ALTER TABLE ' || quote_ident(t) || ' ALTER ' || quote_ident(c) || ' ADD GENERATED BY DEFAULT AS IDENTITY (RESTART ' || v || '); ' | |
|| 'COMMIT;' | |
FROM tab; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
If you recreate the database with Django 4.1 or higher, you do not need to rerun the SQL.