Changing sequences ownership

The following queries allow to change the ownership of the tables and sequences from a schema. Since the sequences were under the ownership of the postgres user, creation of organizations, trips, etc, could not be carried out. In this case all sequences were turn from all schemas were turned over eazytms_eazytms_nightly ownership.

An error occur why running the first query:

DO $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN (
        SELECT sequence_schema, sequence_name 
        FROM information_schema.sequences 
        WHERE sequence_schema = 'public'
    ) LOOP
        BEGIN
            -- Try to change sequence ownership
            EXECUTE 'ALTER SEQUENCE ' || quote_ident(r.sequence_schema) || '.' || quote_ident(r.sequence_name) || ' OWNER TO eazytms_eazytms_nightly;';
        EXCEPTION WHEN OTHERS THEN
            -- If it fails (because it's linked to a table), just ignore and continue
            RAISE NOTICE 'Skipping linked sequence: %', r.sequence_name;
        END;
    END LOOP;
END $$;

Error:

This error occurs because the sequence vehicle_unit_index_seq is bound to a column in the vehicle_unit table (likely an id column using SERIAL). In PostgreSQL, you cannot change the owner of a linked sequence directly; you must change the owner of the Table instead. When you change the table owner, PostgreSQL automatically updates the owner of the linked sequence.

Therefore to be able to change the ownership, we had to execute the process in two steps:

Step 1: Change ownership of all TABLES

Run this query first. This will update all tables and automatically fix the sequences that gave you the error (like vehicle_unit_index_seq).

DO $$
DECLARE
    r RECORD;
BEGIN-- Iterate through all tables in the 'ahola_transport' schemaFOR r IN (
        SELECT table_schema, table_name
        FROM information_schema.tables
        WHERE table_schema = 'ahola_transport'AND table_type = 'BASE TABLE'
    ) LOOP
        -- Change Table OwnershipEXECUTE 'ALTER TABLE ' || quote_ident(r.table_schema) || '.' || quote_ident(r.table_name) || ' OWNER TO eazytms_eazytms_nightly;';
    END LOOP;
END $$;

Step 2: Change ownership of remaining SEQUENCES (Safe Mode)

After running Step 1, there might still be "standalone" sequences that aren't linked to any table. Run this modified script. I have added an exception block so it will skip the ones that are already linked (avoiding the error you just saw) and only update the ones that need it.

DO $$
DECLARE
    r RECORD;
BEGINFOR r IN (
        SELECT sequence_schema, sequence_name 
        FROM information_schema.sequences 
        WHERE sequence_schema = 'ahola_transport'
    ) LOOP
        BEGIN-- Try to change sequence ownershipEXECUTE 'ALTER SEQUENCE ' || quote_ident(r.sequence_schema) || '.' || quote_ident(r.sequence_name) || ' OWNER TO eazytms_eazytms_nightly;';
        EXCEPTION WHEN OTHERS THEN-- If it fails (because it's linked to a table), just ignore and continue
            RAISE NOTICE 'Skipping linked sequence: %', r.sequence_name;
        END;
    END LOOP;
END $$;

The following query shows all sequences in all schemas and their respective owner.

SELECT
    n.nspname AS schema_name,
    c.relname AS sequence_name,
    r.rolname AS owner_name
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_roles r ON r.oid = c.relowner
WHERE c.relkind = 'S'  -- 'S' indicates a Sequence
  -- FIXED LINE BELOW: Use IN (...) to list multiple schemas
  AND n.nspname IN ('ahola_transport', 'at_special', 'generic', 'public')
ORDER BY n.nspname;

Note The names can be used as placeholders so they can be replaced and these queries can be used with any db.