Renaming a column in a PostgreSQL database table

Question

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?

Answer 1

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.

• Faheem Mitha: Thanks for the confirmation. I did try it, and it seemed to work, but I wasn't sure if this was a reliable procedure in general.
• Qwerty: So, if it doesn't break, what are the fkeys attached to?
• András Váczi: @Qwerty I'm not sure I understand what you are asking. Otherwise, the column that is referencing a column of another table is shown in the pg_catalog.pg_constraint.conkey column (the number in that array itself referencing pg_attribute). No column name is involved in this.
• Qwerty: @dezso I was asking about the unique identifier used to attach the fkeys to, if it doesn't use the name. But I did little research on it already, so I am K. Thanks.