PostgreSQL: migrate a primary key from serial (or whatever) to UUID

README.md

Just change the value of the _TABLE_NAME variable (constant):

DO $$
    DECLARE
        _fk TEXT[];
        _primary_key TEXT;
        _column TEXT;
        _foreign_keys TEXT[];
        _TABLE_NAME CONSTANT TEXT NOT NULL := 'the name name of the table to migrate';
        _TEMPORARY_TARGET_COLUMN UUID;
        _TEMPORARY_SOURCE_COLUMN CONSTANT UUID NOT NULL := gen_random_uuid();
    BEGIN
        SELECT
            tc.constraint_name,
            kcu.column_name
        INTO STRICT
            _primary_key,
            _column
        FROM information_schema.table_constraints AS tc
        JOIN information_schema.key_column_usage AS kcu
            ON tc.constraint_name = kcu.constraint_name
            AND tc.table_schema = kcu.table_schema
        JOIN information_schema.constraint_column_usage AS ccu
            ON ccu.constraint_name = tc.constraint_name
            AND ccu.table_schema = tc.table_schema
        WHERE
            TRUE
            AND tc.constraint_type = 'PRIMARY KEY'
            AND ccu.table_name = _TABLE_NAME
        ;

        SELECT
            COALESCE(
                array_agg(
                    ARRAY[tc.table_name, kcu.column_name, rc.update_rule, rc.delete_rule]
                ),
                ARRAY[]::TEXT[]
            )
        INTO _foreign_keys
        FROM information_schema.table_constraints AS tc
        JOIN information_schema.key_column_usage AS kcu
            ON tc.constraint_name = kcu.constraint_name
            AND tc.table_schema = kcu.table_schema
        JOIN information_schema.constraint_column_usage AS ccu
            ON ccu.constraint_name = tc.constraint_name
            AND ccu.table_schema = tc.table_schema
        JOIN information_schema.referential_constraints AS rc
            ON rc.constraint_name = tc.constraint_name
        WHERE
            TRUE
            AND tc.constraint_type = 'FOREIGN KEY'
            AND ccu.table_name = _TABLE_NAME
        ;

        -- RAISE NOTICE '_column = %', _column;
        -- RAISE NOTICE '_primary_key = %', _primary_key;
        -- RAISE NOTICE '_foreign_keys = %', _foreign_keys;

        EXECUTE FORMAT('ALTER TABLE %I DROP CONSTRAINT %I CASCADE', _TABLE_NAME, _primary_key);
        EXECUTE FORMAT('ALTER TABLE %I RENAME COLUMN %I TO %I', _TABLE_NAME, _column, _TEMPORARY_SOURCE_COLUMN);
        EXECUTE FORMAT('ALTER TABLE %I ADD COLUMN %I UUID NOT NULL DEFAULT gen_random_uuid()', _TABLE_NAME, _column);
        EXECUTE FORMAT('ALTER TABLE %I ADD PRIMARY KEY(%I)', _TABLE_NAME, _column);
        IF _foreign_keys IS DISTINCT FROM ARRAY[]::TEXT[] THEN
            FOREACH _fk SLICE 1 IN ARRAY _foreign_keys
            LOOP
                _TEMPORARY_TARGET_COLUMN := gen_random_uuid();
                RAISE NOTICE '<handling FK %.% references %.%>', _fk[1], _fk[2], _TABLE_NAME, _column;
                EXECUTE FORMAT('ALTER TABLE %I RENAME COLUMN %I TO %I', _fk[1], _fk[2], _TEMPORARY_TARGET_COLUMN);
                EXECUTE FORMAT('ALTER TABLE %I ADD COLUMN %I UUID', _fk[1], _fk[2]);
                EXECUTE FORMAT('UPDATE %I SET %I = (SELECT %I FROM %I WHERE %I.%I = %I.%I)', _fk[1], _fk[2], _column, _TABLE_NAME, _TABLE_NAME, _TEMPORARY_SOURCE_COLUMN, _fk[1], _TEMPORARY_TARGET_COLUMN);
                EXECUTE FORMAT('ALTER TABLE %I ALTER COLUMN %I DROP NOT NULL', _fk[1], _TEMPORARY_TARGET_COLUMN);
                EXECUTE FORMAT('ALTER TABLE %I ALTER COLUMN %I SET NOT NULL', _fk[1], _fk[2]);
                EXECUTE FORMAT('ALTER TABLE %I ADD FOREIGN KEY(%I) REFERENCES %I(%I) ON UPDATE %s ON DELETE %s', _fk[1], _fk[2], _TABLE_NAME, _column, _fk[3], _fk[4]);
                EXECUTE FORMAT('CREATE INDEX ON %I(%I);', _fk[1], _fk[2]);
                RAISE NOTICE '</handling FK %.% references %.%>', _fk[1], _fk[2], _TABLE_NAME, _column;
            END LOOP;
        END IF;
        EXECUTE FORMAT('ALTER TABLE %I ALTER COLUMN %I DROP NOT NULL', _TABLE_NAME, _TEMPORARY_SOURCE_COLUMN);
        -- EXECUTE FORMAT('ALTER TABLE %I DROP COLUMN %I', _TABLE_NAME, _TEMPORARY_SOURCE_COLUMN);
    END;
$$ LANGUAGE plpgsql;