How to Single Query to Rename and Change Column Type in PostgreSQL

1. Rename and Change Column Type in MySQL
2. the Simple ALTER Table Query to Change Data Type and Column Name in PostgreSQL
3. Create a User-Specific Function to Perform Both Queries Together in PostgreSQL

This article describes using just a single query to rename a column and change its type in PostgreSQL.

Rename and Change Column Type in MySQL

In MySQL, if you want to change a column type and rename it, you can use a simple statement such as below.

alter table TAB change id identity varchar(50);

The preceding statements include the table TAB and its column ID as INT.

create table TAB(
    id int not null,
    name varchar(50) not null
);

How does this work? The CHANGE is an extension only provided by MySQL compared to other SQL queries; it uses the ALTER statement and follows this convention.

ALTER TABLE tbl_name [alter_option [, alter_option] ...] [partition_options]

For the alter_option, you can use:

CHANGE [COLUMN] old_col_name new_col_name column_definition
        [FIRST | AFTER col_name]

This syntax helps you use the CHANGE statement effectively. You first type out the table name and then call the CHANGE for the column you want to change.

There’s a column_definition, where you place the updated data type of this column.

Now that we have understood this MySQL extension in detail let’s look at how we can do the same thing in PostgreSQL.

the Simple ALTER Table Query to Change Data Type and Column Name in PostgreSQL

No single ALTER statement in PostgreSQL would perform something like this.

ALTER TABLE cat
	ALTER COLUMN id TYPE varchar(50)
	RENAME id TO identity;

The above is wrong and will return an error. The best way to do this is to use multiple ALTER statements.

ALTER TABLE cat
	ALTER COLUMN id TYPE varchar(50);
ALTER TABLE cat
	RENAME id TO identity;

Why? Because PostgreSQL does not have a notation that specifies multiple operations inside a single ALTER statement.

You can see that each ALTER statement supports only one operation at a time. Hence, if you call ACTION for columns after the ALTER statement, you can only ADD, ALTER, or DROP columns.

You have to call the ALTER statement again to RENAME the column.

Create a User-Specific Function to Perform Both Queries Together in PostgreSQL

However, if you want to compile both operations together, you can make a function that tends to do that.

create or replace function alter_change_extension(new_type varchar(50), new_name varchar(50))
	returns void
	language plpgsql
	as
$$
begin
if new_type = 'varchar(50)' then
	ALTER TABLE cat ALTER COLUMN id TYPE varchar(50);
end if;
if new_name = 'identity' then
	ALTER TABLE cat RENAME id TO identity;
end if;
end;
$$;

Here, you declare two variables for the TYPE and NEW NAME you want to change the column. You make IF statements to check if the values exist, then if they are true, you go ahead and ALTER the column properly.

You may use similar IF statements for specific case scenarios and make your function dynamic.

This method is not so efficient as there may be unique strings passed each time, and adding a lot of IF statements would make matters worse. However, you only need a single query then to execute this.

select alter_change_extension('varchar(50)', 'identity');