Postgresql Query to Rename and Change Column Type with Single Query
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 to and to Change...
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>
2 Answers
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?
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.
James Sterling reports on renewable energy developments, climate policy, ecological conservation, and green tech innovations around the globe.
