JIYIK CN >

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

Renaming and changing column types in MySQL

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

alter table TAB change id identity varchar(50);

The preceding statement includes the table TABand its columns IDas INT.

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

How does this work? SQLCompared to other queries, the SELECT statement CHANGEis MySQLan extension provided only by ; it uses ALTERthe SELECT statement and follows this convention.

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

For that 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 INSERT statement efficiently CHANGE. You first enter tablethe name and then the INSERT for the column you want to change CHANGE.

There is one column_definitionwhere you can put the updated data type for this column.

Now that we have looked at this MySQLextension in detail, let's see how we can do the same thing in PostgreSQL.

ALTERSimple table query to change data type and column name in PostgreSQL

There is no single ALTERstatement in PostgreSQL that does such an operation.

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

The above is wrong and will return an error. The best approach is to use multiple ALTERstatements.

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

Why? Because PostgreSQL has no ALTERnotation for specifying multiple operations in a single statement.

You can see that each ALTERstatement supports only one operation at a time. Therefore, if you ALTERcall it for a column after a statement ACTION, you can only use ADD, , ALTERor DROPcolumns.

You must call ALTERthe statement again to RENAMEdelete the columns.

Create a user-specific function in PostgreSQL to execute two queries simultaneously

However, if you want to compile both operations at the same time, you can create a function that tends to do this.

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 TYPEand for the columns you want to change NEW NAME. You make IFstatements to check if the values ​​exist, and then if they are true, you go ahead and change the correct ALTERcolumn.

You can use similar IFstatements for specific case scenarios and make your functions dynamic.

This approach is not very efficient because you might be passing a unique string each time, and adding a lot of IFstatements will make things worse. However, you only need one query to do this.

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