PostgreSQL query to rename and change column type with single query

Question

In PostgreSQL if I need to rename and change a column data type, I run two separate queries to do so.

To rename:

ALTER TABLE tblName RENAME <oldColumn> TO <newColumn>

and to change column type:

ALTER TABLE tblName ALTER COLUMN <newColumn> <columnType>.

But is there any way to do both of these works with a single query like the following MySQL query:

ALTER TABLE tblName CHANGE COLUMN <oldColumn> <newColumn> <columnType>

I've removed the references to MS SQL Server. These sorts of "how do I do this on these different DBs" questions rarely lead to a single useful definitive answer. Please post a new, separate question for MS-SQL Server and link to it here. It would also be useful to know why you want to do this in the first place, as in this case there's no benefit to using a single ALTER TABLE statement vs two within the same transaction. — Craig Ringer
– Craig Ringer, Commented Aug 20, 2014 at 7:09
@dude When editing, please don't use backticks for emphasis. If you need to emphasise a piece of text, wrap it in **two asterisks** — Bojangles
– Bojangles, Commented Aug 20, 2014 at 7:11

Craig Ringer · Accepted Answer · 2014-08-20 07:07:15Z

129

In PostgreSQL, ALTER TABLE can take a series of operations. So:

ALTER TABLE <tablename> RENAME <oldcolumn> TO <newcolumn>;
ALTER TABLE <tablename> ALTER COLUMN <columnname> TYPE <newtype>;

is the same as

ALTER TABLE <tablename> 
  ALTER COLUMN <columnname> TYPE <newtype>
  RENAME <oldcolumn> TO <newcolumn>;

However... why? IIRC the rename won't cause a full-table scan, so there's no benefit over just doing the two statements separately, within one transaction. What problem are you actually trying to solve with this?

answered Aug 20, 2014 at 7:07

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

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

7 Comments

taufique Over a year ago

What about SQL Server? Is it possible in SQL server too?

Craig Ringer Over a year ago

@taufique Please see my first comment on your question. It's best not to post "how do I do X in this list of databases" questions, because different people's expertise differs. See the comment on your post. I have no idea if it's possible on SQL server. Posting a separate question for SQL Server would be appropriate; then post a link to it here. But again - why do you want to do this in the first place, when it makes no difference?

Laurenz Albe Over a year ago

You cannot use RENAME together with other actions in a single ALTER TABLE statement.

Craig Ringer Over a year ago

Poor choice of example then, oops.

McCray Over a year ago

I don't know if this answer is because it is too old, this way of writing is definitely not applicable to the current postgresql.

|

Craig Ringer · Accepted Answer · 2014-08-20 07:29:33Z

2

PostgreSQL: Alter table column name and data-type:

ALTER TABLE <TableName> 
   ALTER [ COLUMN ] column [ SET DATA ] TYPE data_type [ COLLATE collation ] [ USING expression ]
  RENAME [ COLUMN ] column TO new_column;

See ALTER TABLE.

edited Aug 20, 2014 at 7:29

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

answered Aug 20, 2014 at 7:13

Vikram Jain

5,6081 gold badge22 silver badges31 bronze badges

Comments

zacronos · Accepted Answer · 2025-02-13 20:06:08Z

As of this writing, with PostgreSQL you cannot rename a column while also changing its type (or performing any other change), nor has this ever been possible.

Ever since ALTER TABLE was first allowed to include a list of actions (v8.0) through the current version (v17, as of this answer), the RENAME form of the command has never been allowed to be part of a list of actions; it must be in a command by itself.

See ALTER TABLE, specifically where it says (bold added for emphasis):

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.

Collectives™ on Stack Overflow

PostgreSQL query to rename and change column type with single query

3 Answers 3

7 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related