Postgresql – Renaming a column in a PostgreSQL database table

postgresql

This is essentially the same question as How do I rename a column in a database table using SQL?.

So, I want to rename a column in a table. This column is part of a set of tables that lives inside a PostgreSQL schema, which is itself in a PostgreSQL database.

However, I also have constraints like foreign keys involving this column.
It is not clear to me whether this command will also take care of constraints. If not, how should I handle this?

Alternatively, I also have this database available as a dump file. Would it make sense to edit the dump file, and if so, how?

Best Answer

You could try it yourself as well, but no: renaming a column won't break your foreign keys.

Try this:

CREATE TABLE a (a_id serial PRIMARY KEY);

CREATE TABLE b (a_id integer REFERENCES a (a_id));

ALTER TABLE a RENAME COLUMN a_id TO id;

and have a look at b's definition.

Imagine the world if it weren't so: every single column renaming (in my experience happening a lot in the first phases of development) would involve modifying constraints here and there. Wouldn't make much sense, would it?

Doing this in a dump is rather error-prone, I would not do that.

Related Question