CODEWITHSUNDEEP
postgresql
Tom
Tom

Reputation: 1468

PostgreSQL rename table - cascade renaming to objects like indices, constraints that contain table name

I renamed my tables with ALTER TABLE _ RENAME TO _:

ALTER TABLE foods RENAME TO food;
ALTER TABLE foods_nutrients RENAME TO food_nutrient;

It worked, however other database objects like indices, sequences and constraints that contained these table names didn't update. So I had to update them manually like this:

ALTER INDEX foods_pkey RENAME TO food_pkey;
ALTER SEQUENCE foods_id_seq RENAME TO food_id_seq;
ALTER TABLE food_nutrient RENAME CONSTRAINT foods_nutrients_food_id_fkey TO food_nutrient_food_id_fkey;

It wasn't necessary to rename them but it doesn't feel right to not do so. I wonder if there is command that does this automatically. Or is it not common to rename all objects containing the old table name?

Upvotes: 9

Views: 3078

Answers (2)

n0099
n0099

Reputation: 1333

This will rename any index which name not following $tableName_$columnName(composite index will concat each name of column component with _), primary key index for $tableName_pkey and other rules for constraint name: PostgreSQL: default constraint names, note it's possible to override the default rule when CREATE TABLE: PostgreSQL: is it possible to provide custom name for PRIMARY KEY or UNIQUE?

-- https://stackoverflow.com/questions/65069778/postgresql-rename-table-cascade-renaming-to-objects-like-indices-constraints/78485773#78485773
DO $$DECLARE r record;
BEGIN
    FOR r IN
        SELECT 'ALTER INDEX "' || schema_name || '"."' || index_name ||
               '" RENAME TO "' || new_index_name || '";' sql, *
        FROM (
            SELECT schema_name, table_name, index_name,
                   table_name ||
                   CASE constraint_type
                       -- only uncomment below line when using postgresql<12 https://stackoverflow.com/questions/4107915/postgresql-default-constraint-names#comment109084417_4108266
                       -- WHEN 'f' THEN '_' || columns[1] -- https://stackoverflow.com/questions/4107915/postgresql-default-constraint-names#comment127573826_4108266
                       WHEN 'p' THEN '' -- https://stackoverflow.com/questions/4107915/postgresql-default-constraint-names#comment127553786_4108266
                       ELSE '_' || string_agg(column_name, '_' ORDER BY colum_index)
                   END || '_' ||
                   -- https://stackoverflow.com/questions/15417167/case-when-null-evaluates-to-false
                   CASE COALESCE(constraint_type, 'NULL')
                       -- https://stackoverflow.com/questions/4107915/postgresql-default-constraint-names/4108266#4108266
                       -- https://gist.github.com/popravich/d6816ef1653329fb1745
                       -- https://stackoverflow.com/questions/8674562/postgresql-is-it-possible-to-provide-custom-name-for-primary-key-or-unique/8674640#8674640
                       WHEN 'c' THEN 'check'
                       WHEN 'f' THEN 'fkey' -- TODO: foreign key constraint only exists on the referencing table
                       WHEN 'p' THEN 'pkey'
                       WHEN 'u' THEN 'key'
                       WHEN 'x' THEN 'excl'
                       WHEN 'NULL' THEN 'idx'
                   END new_index_name
            FROM (
                SELECT n.nspname schema_name, t.relname table_name, i.relname index_name, c.contype constraint_type, a.attname column_name, a.attnum colum_index
                FROM pg_index x
                -- https://stackoverflow.com/questions/37329561/how-to-list-indexes-created-for-table-in-postgres/37330092#37330092
                JOIN pg_class t ON t.oid = x.indrelid
                JOIN pg_namespace n ON n.oid = t.relnamespace
                JOIN pg_class i ON i.oid = x.indexrelid
                -- https://stackoverflow.com/questions/55447819/how-to-get-the-list-of-column-names-for-all-indexes-on-a-table-in-postgresql/55448707#55448707
                JOIN pg_attribute a ON a.attrelid = x.indrelid AND a.attnum = ANY(x.indkey)
                -- https://www.postgresql.org/docs/current/catalog-pg-constraint.html
                LEFT JOIN pg_constraint c ON c.conindid = x.indexrelid AND c.conrelid = t.oid
                WHERE n.nspname = 'your_schema'
            ) t
            GROUP BY schema_name, table_name, index_name, constraint_type
        ) t
        WHERE index_name != new_index_name
    LOOP
         -- https://stackoverflow.com/questions/1348126/postgresql-modify-owner-on-all-tables-simultaneously-in-postgresql/37259655#37259655
         EXECUTE r.sql;
    END LOOP;
END$$;

Upvotes: 0

Sebastian Redl
Sebastian Redl

Reputation: 71909

There is no technical relation between the names of indices, sequences, etc and any table names. So there is no reliable way to rename them automatically.

You can write a script that goes through the schema and renames things by search & replace, but that carries the risk of hitting false positives.

Upvotes: 5

Related Questions