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

Question

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?

Besides not feeling right, it may also produce an actual conflict error (it did that for me), when another table is being created with the same old name. As constraint names are normally based on the table name, creating a constraint for the new table (or by table creation statement itself) may result in an error as such constraint name may already exist for the now differently named table. — Jānis Elmeris
– Jānis Elmeris, Commented Jul 18, 2024 at 14:20

Sebastian Redl · Accepted Answer · 2020-11-30 07:57:38Z

5

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.

answered Nov 30, 2020 at 7:57

Sebastian Redl

73.9k8 gold badges137 silver badges177 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

n0099 · Accepted Answer · 2024-08-18 16:41:40Z

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$$;

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related