How to Change a Column Name in PostgreSQL

Renaming a column in PostgreSQL is a common task when evolving your database schema. Fortunately, PostgreSQL makes this process easy and safe using the ALTER TABLE command.

Why Rename a Column?

As your application evolves, you might need to rename a column to improve clarity, correct a typo, or reflect new business requirements. Doing this properly ensures your data stays intact and your queries continue to work smoothly.

Basic Syntax

To rename a column, use the following SQL syntax:

ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name;

Example

Let’s say you have a table named customers with a column called fullname and you want to rename it to full_name. You would run:

ALTER TABLE customers RENAME COLUMN fullname TO full_name;

PostgreSQL will automatically update the table definition. Any views, functions, or applications using the old name will need to be updated manually.

Important Considerations

• Check for dependent objects like indexes, views, or triggers that reference the column.
• Update your application code, ORMs, and queries to use the new column name.
• If using replication or logical decoding, test the change in a staging environment first.

Best Practices

• Always make a backup before altering your schema.
• Use a migration tool if you manage your schema with migrations (e.g., Django migrations, Alembic, Liquibase).
• Communicate schema changes with your development team to avoid breaking downstream systems.

Summary

Renaming a column in PostgreSQL is straightforward with ALTER TABLE ... RENAME COLUMN. Just remember to review dependencies, update your codebase, and follow best practices to ensure a smooth transition.