In this article, we will see how to change column datatype in PostgreSQL. We can change the data type of a column by using the ALTER TABLE statement.
Two scenarios are there:
1. Change column datatype of table with data.
2. Change column datatype of table without data.
PostgreSQL change column data type statement:
To change the data type of a column, you use the ALTER TABLE statement as follows:
ALTER TABLE table_name ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type [ COLLATE collation ] [ USING expression ]
Change column datatype of table with data.
1. Lets create table salgrade with following syntax:
create table salgrade(grade int,lowsal money,highsal money);
See the table structure by using \d table_meta command.
2. Now change the lowsal and highsal columns datatype from money to numeric.
alter table salgrade alter lowsal type numeric using lowsal::numeric;
alter table salgrade alter highsal type numeric using highsal::numeric;
Change column datatype of table without data.
1. Lets create table grade with following syntax:
create table grade(grade int,sal money);
2. Now change the sal datatype from money to numeric.
alter table grade alter column sal type numeric;