Renaming multiple columns in one statement with PostgreSQL

sql postgresql ddl alter-table table-rename
Rovanion picture Rovanion · Apr 24, 2014 · Viewed 29.2k times · Source

Is it possible to rename multiple columns in a single statement, something along the lines of:

ALTER TABLE Users
    RENAME COLUMN userName TO user_name, 
    RENAME COLUMN realName TO real_name;

Answer

Erwin Brandstetter picture Erwin Brandstetter · Apr 24, 2014

No.

While other actions can be combined, that's not possible with RENAME. The manual:

All the forms of ALTER TABLE that act on a single table, except RENAME, SET SCHEMA, ATTACH PARTITION, and DETACH PARTITION can be combined into a list of multiple alterations to be applied together.

Since RENAME is a tiny operation on a system catalog, there is no harm in running multiple statements. Do it in a single transaction to minimize locking overhead.

Other actions like ALTER COLUMN ... SET TYPE are potentially expensive because they may have to rewrite the whole table. With big tables it would be wise to do as much as possible in a single statement.

Related questions

Change column type from timestamp WITHOUT time zone to timestamp WITH time zone

I found this ALTER COLUMN statement in the PostgreSQL 9.3 ALTER TABLE manual page: ALTER TABLE audits ALTER COLUMN created_at SET DATA TYPE timestamp with time zone USING timestamp with time zone 'epoch' + created_at * interval '1 second'; I cannot …

Read More
Give all the permissions to a user on a DB

I would like to give an user all the permissions on a database without making it an admin. The reason why I want to do that is that at the moment DEV and PROD are different DBs on the same …

Read More
PostgreSQL create table if not exists

In a MySQL script you can write: CREATE TABLE IF NOT EXISTS foo ...; ... other stuff ... and then you can run the script many times without re-creating the table. How do you do this in PostgreSQL?

Read More