A script that outputs the raw SQL to convert Django's serial columns to identity columns.

serial-to-identity.sql

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;

Cool! Thank you for posting this! I just had to remove the single quotes around table names (or use double quotes) in the generated sql, then it worked perfect!

I found this gist from a great article that helped me understand this change in Django 4.1

Does any know if any changes need to be made to the migrations of Django after runing the SQL manually to change the columns? Like if I ran the migrations to recreate my database, would it create the tables using the old serial type of columns?

Does any know if any changes need to be made to the migrations of Django after runing the SQL manually to change the columns? Like if I ran the migrations to recreate my database, would it create the tables using the old serial type of columns?

If you recreate the database with Django 4.1 or higher, you do not need to rerun the SQL.