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

1. 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

2. 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

3. 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?

Answer 1

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.