Alter Multiple column Name and Data Type in PostgreSQL in one query

Question

Description: I want to change column name and its data type. I have multiple columns and want to change all in one query along with their datatype.

What i tried

Changing column type then renaming it.

ALTER TABLE customers  ALTER COLUMN phone TYPE numeric 
RENAME COLUMN phone TO contact_phone

and

ALTER TABLE customers  ALTER COLUMN phone TYPE numeric,
RENAME COLUMN phone TO contact_phone

Changing multiple column type then renaming it.

ALTER TABLE customers  
  ALTER COLUMN phone TYPE numeric, 
  ALTER COLUMN address TYPE text, 
  RENAME COLUMN phone TO contact_phone,
  RENAME COLUMN address TO contact_address

Tried to change all column data type then rename it

ALTER TABLE customers  
ALTER COLUMN phone TYPE numeric,ALTER COLUMN address TYPE text

and

ALTER TABLE customers 
 RENAME COLUMN phone TO contact_phone,RENAME COLUMN address TO contact_address

Issue: Each time I am getting an error in the RENAME clause which is SQL State : 42601

Can anyone tell me what is wrong with this query?

Laurenz Albe · Accepted Answer · 2018-11-05 08:41:45Z

5

While you can change the data type of several columns in one ALTER TABLE statement, renaming a column can only be done one at a time. So you will have to use several ALTER TABLE statements.

I would recommend to run all statements in a single transaction, that way you have to acquire the ACCESS EXCLUSIVE lock only once.

edited Nov 5, 2018 at 8:41

answered Nov 5, 2018 at 8:35

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

7 Comments

Zohaib Waqar Over a year ago

i tried this as well .. used two alter statements in first i changed datatypes and in second i renamed columns. but got issue in renaming. kindly look at third point in my question under what i tried heading

Laurenz Albe Over a year ago

I see. I have adapted the answer.

Zohaib Waqar Over a year ago

i just tried in editor instead of query and i noticed i am getting error in renaming multiple column in one query. ALTER TABLE customers RENAME COLUMN phone TO contact_phone,RENAME COLUMN address TO contact_address RENAME multiple time is not acceptable by editor i think. i tried without putting comma before next RENAME clause still issue.

Laurenz Albe Over a year ago

The tutorial is plain wrong. Look at the documentation instead.

Laurenz Albe Over a year ago

Exactly! If you look at the syntax diagram for RENAME (of a column), you will see that there are no provisions for more than one such clause.

|

Collectives™ on Stack Overflow

Alter Multiple column Name and Data Type in PostgreSQL in one query

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related