How to Change Column Type in Postgres
This article shows how to change the column type to another data type in Postgres.
Use the ALTER TABLE Command to Change Column Type in Postgres
ALTER TABLE <table_name>
ALTER COLUMN <column_name> [SET DATA] TYPE <new_type>;
Use the table name, column name and the new data type. For example:
CREATE TABLE student(
id SERIAL PRIMARY KEY,
name VARCHAR NOT NULL,
admission_date DATE NOT NULL,
contact_no INT NOT NULL,
description TEXT
);
Output:
postgres=# \d student
Table "public.student"
Column | Type | Collation | Nullable | Default
----------------+-------------------+-----------+----------+-------------------------------------
id | integer | | not null | nextval('student_id_seq'::regclass)
name | character varying | | not null |
admission_date | date | | not null |
contact_no | integer | | not null |
description | text | | |
Indexes:
"student_pkey" PRIMARY KEY, btree (id)
postgres=#
To change the data type of the contact number of the student and change it to VARCHAR, the contact number can have + or - between.
So the command for changing the data type of the above column:
ALTER TABLE student
ALTER COLUMN contact_no TYPE VARCHAR;
The table description:
postgres=# ALTER TABLE student
postgres-# ALTER COLUMN contact_no TYPE VARCHAR;
ALTER TABLE
postgres=# \d student;
Table "public.student"
Column | Type | Collation | Nullable | Default
----------------+-------------------+-----------+----------+-------------------------------------
id | integer | | not null | nextval('student_id_seq'::regclass)
name | character varying | | not null |
admission_date | date | | not null |
contact_no | character varying | | not null |
description | text | | |
Indexes:
"student_pkey" PRIMARY KEY, btree (id)
postgres=#
If the table is populated with some rows, and in the contact_no column, you have VARCHAR or non-numeric value.
If you try again to change the data type of the contact_no to int, then Postgres will show you an error called, You might need to specify USING <column_name>::<data_type>.
Insert a row with the following SQL command:
INSERT INTO STUDENT(name,admission_date,contact_no,description)
VALUES('John Doe','2022-01-01','1212125856 ','Lorem ipsum');
Ran the statement for changing the data type:
ALTER TABLE student
ALTER COLUMN contact_no TYPE INT;
Error:
ERROR: column "contact_no" cannot be cast automatically to type integer
HINT: You might need to specify "USING contact_no::integer".
So, you need to add that line too.
ALTER TABLE student
ALTER COLUMN contact_no TYPE VARCHAR
USING contact_no::integer;
Now, the above SQL command will be accepted. But, VARCHAR might contain leading or trailing white spaces, so you need to trim the white spaces.
The updated command will look like this:
ALTER TABLE student ALTER COLUMN contact_no TYPE TEXT USING contact_no::integer;
ALTER TABLE student ALTER COLUMN contact_no TYPE VARCHAR USING trim(contact_no);
