Renaming multiple columns in one statement with PostgreSQL

Question

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;

Erwin Brandstetter · Accepted Answer · 2021-05-22 22:49:32Z

91

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 and avoid race conditions with intermediate states (which are then invisible to all other transactions).

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.

edited May 22, 2021 at 22:49

answered Apr 24, 2014 at 16:41

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Renaming multiple columns in one statement with PostgreSQL

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related