How to change the type of a column in PostgreSQL?

In order to change the type of a column, the syntax is

ALTER TABLE table_name
ALTER COLUMN column_name TYPE new_data_type USING expression;

Please note that altering the type of a column is not recommended generally, especially if your table has a lot of entries already.

The USING part of the syntax is optional. It is used when you need to use an expression for converting the existing entries in that column from the current type to the new type. For instance, if you convert a column of type INTEGER to type BIGINTEGER, you need not use any expression for the conversion of existing entries to the new types.

However, if you convert a TEXT or a VARCHAR entry to INTEGER, you will not be able to do that by default, unless you provide an expression for converting the existing TEXT/VARCHAR values to an integer.

Let us understand both the cases using an example. Let us create a new table marks −

CREATE TABLE marks(
   serial_no INTEGER PRIMARY KEY,
   name VARCHAR,
   roll_no INTEGER,
   marks_obtained INTEGER,
   perc_marks DOUBLE PRECISION,
   max_marks INTEGER,
   date_of_entry DATE
);

Now, let us populate it

INSERT INTO marks(serial_no,name, roll_no, marks_obtained,
perc_marks, max_marks, date_of_entry)
VALUES (1,'Yash', 26, 42, 42.0, 100, current_date),
(2,'Isha', 56, 175, 87.5, 200, current_date);

Now, suppose we want to convert the serial_no column from type INTEGER to BIGINTEGER. We need not use any expression for converting the column type.

ALTER TABLE marks
ALTER COLUMN serial_no TYPE BIGINT;

Now, for understanding the cases where we need to use an expression, let us first convert the roll_no column to VARCHAR type.

ALTER TABLE marks
ALTER COLUMN roll_no TYPE VARCHAR;

This statement will go through. Because integer to text conversion does not confuse PostgreSQL. It simply replaces the digit with the character representing that digit. 26 becomes ‘26’. Now, let us try to convert the roll_no column back to an integer

ALTER TABLE marks
ALTER COLUMN roll_no TYPE INTEGER;

PostgreSQL will now throw up an error. This is because the conversion of text to integer is not that straightforward for PostgreSQL. Specifically, the error will read −

ERROR: column "roll_no" cannot be cast automatically to type integer HINT: You might need to specify "USING roll_no::integer". SQL state: 42804

This is great. PostgreSQL is even providing us with a hint. It is asking us to cast the roll_no values into integer type explicitly. :: represents CAST operator in PostgreSQL.

Let us take PostgreSQL’s hint and run −

ALTER TABLE marks
ALTER COLUMN roll_no TYPE INTEGER USING roll_no::integer;

It will work now.

Yash Sanghvi

e

Updated on: 02-Feb-2021

4K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started