CODEWITHSUNDEEP
sqlpostgresqlalter-table
gwatt121
gwatt121

Reputation: 43

Syntax error while using multiple rename RENAME expressions postgresql

I'm trying to write a query that RENAMEs multiple table columns at once. According to the documentation, the syntax is:

ALTER TABLE table_name
RENAME old_col_a AS new_col_a
, RENAME old_col_b AS new_col_b...;

However, in doing so I get a syntax error located on the comma after the first RENAME clause:

ERROR:  syntax error at or near ","
LINE 3: , RENAME 
    ^
SQL state: 42601
Character: 1

The query works for multiple DROP/ALTER/ADD columns and for single RENAMEs. I just can't for the life of me figure out why this error is occurring.

Upvotes: 3

Views: 5500

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175606

You need to use multiple ALTER statements:

ALTER TABLE table_name
RENAME COLUMN old_col_a TO new_col_a;

ALTER TABLE table_name
RENAME COLUMN old_col_b TO new_col_b;

ALTER TABLE

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. For example, it is possible to add several columns and/or alter the type of several columns in a single command. This is particularly useful with large tables, since only one pass over the table need be made.

Upvotes: 6

Related Questions