ALTER TABLE and Renaming

Objectives

By the end of this lesson, you should be able to:

• Explain what ALTER TABLE is for
• Rename a table
• Rename a column

💡 Why this matters: Module 3 built tables, Module 4 filled them with data. Real tables still need to change after that, a name that no longer fits, a column that needs relabeling. ALTER TABLE is the statement behind every change in this module, starting with the simplest: renaming things.

⚠️ A note on verification: every statement and result in this lesson was run against a real, live PostgreSQL 18 database, using the employees table from Module 3.

ALTER TABLE: One Statement, Many Jobs

ALTER TABLE is the umbrella statement for changing an existing table’s structure. Every lesson in this module is really ALTER TABLE table_name followed by a different clause, renaming, adding or removing a column, changing a type, adding or removing a constraint. Learning the pattern once makes every variation in this module easy to recognize.

Renaming a Table

ALTER TABLE employees RENAME TO staff;

table_name
----------
staff

The table’s data, columns, and constraints are completely unaffected, only its name changes. Every reference to the old name, in other queries, in foreign keys pointing at it, needs updating too, which is exactly why renaming a table already in real use is a bigger decision than it looks.

Renaming a Column

ALTER TABLE employees RENAME COLUMN department TO team;

column_name | is_nullable
-------------+-------------
 id          | NO
 first_name  | NO
 last_name   | NO
 email       | NO
 team        | YES
 salary      | NO

Just like renaming a table, the column’s data and type are untouched, only its name changes. SELECT * FROM employees; now shows team instead of department, with every existing value still intact underneath.

Try It

1. Rename employees to staff, confirm the new name with a query against information_schema.tables, then rename it back to employees.
2. Rename the department column to team, confirm with a query against information_schema.columns, then rename it back to department.
3. Explain, in your own words, why renaming a table that other queries or applications already depend on needs more care than renaming a brand-new one.

Recap

• ALTER TABLE table_name ... is the general statement behind every structural change in this module.
• ALTER TABLE ... RENAME TO new_name renames a table.
• ALTER TABLE ... RENAME COLUMN old_name TO new_name renames a column, in both cases the data itself is untouched.

Next lesson: adding and dropping columns entirely.