CODEWITHSUNDEEP
postgresql
Laurence Gonsalves
Laurence Gonsalves

Reputation: 143114

How to practically rename tables and columns in PostgreSQL on a production system?

It often happens that the original name something is given is not the best name. Maybe requirements shifted slightly, or maybe as time went on, a better understanding of the concept being represented developed. Sometimes one name is used in developing a feature, but testing with real users reveals that a better name is needed, and it'd be nice to have the names for things in the DB match the names used in the UI.

PostgreSQL lets you rename tables and columns via alter table, but often this isn't feasible in production without significant downtime. If existing clients are using the old name, you can't just yank it out from under them.

I was hoping there was some way to "add a name" to a table or column, so that old_name and new_name will both work, and then at a later time remove the old name. Then a name migration could work like this:

Is there a way to do this? If not, is there a recommended procedure for renaming a column while minimizing downtime? How about a table?

Upvotes: 20

Views: 10515

Answers (1)

Laurence Gonsalves
Laurence Gonsalves

Reputation: 143114

Some recipes for renaming tables and/or columns in a production system that seem to work. However, I've only tested these on a small test database, not on a large production system. Renaming and view creation are both supposed to be very fast, though.

Renaming a table

  1. Rename the table, and temporarily add an updatable view with the old name:

    begin;
    alter table old_table_name rename to new_table_name;

    create view old_table_name as
        select * from new_table_name;
commit;

  2. Migrate clients to use the new table name.

  3. Once all clients are migrated, drop the view:

    drop view old_table_name;

Renaming columns without renaming the table

Renaming a column without renaming the table is a bit more complicated, because we can't have a view shadow a table (apparently).

  1. Rename the column(s), temporarily rename the table, and add an updatable view that adds the old name for the column with the table's correct name:

    begin;
    alter table my_table rename column old_column_name to new_column_name;

    alter table my_table rename to my_table_tmp;

    create view my_table as
        select *, new_column_name as old_column_name
        from my_table_tmp;

commit;

  2. Migrate clients to use the new column name.

  3. Once all clients are migrated, drop the view, rename the table back:

    begin;
    drop view my_table;

    alter table my_table_tmp rename to my_table;
commit;

Renaming a table and some of its columns simultaneously

  1. Rename the table and columns, and temporarily add an updatable view with the old name and old columns:

    begin;
    alter table old_table_name
        rename to new_table_name;

    alter table new_table_name
        rename column old_column_name to new_column_name;

    create view old_table_name as
        select *, old_column_name as new_column_name
        from new_table_name;
commit;

    Instead of select *, old_column_name as new_column_name, it might be better to only have the original set of columns, so clients have to migrate to the new table name to get the new column names:

    create view old_table_name as
    select
        unchanged_name1,
        unchanged_name2,
        ...
        old_column_name as new_column_name
    from new_table_name;

  2. Migrate clients to use the new table and column names.

  3. Once all clients are migrated, drop the view:

    drop view old_table_name;

Upvotes: 23

Related Questions