converting varchar column to integer in a table where there's already data

Question

In a table with data where I have a column of type varchar(255). The data I have in the column are numbers, meaning a single number in that column in each row. I want to convert its data type to integer. How can I do that easily? Should I merely change its type to integer? Or create a migration to convert data in it also?

Answer 1

You can use ALTER TABLE with USING:

ALTER TABLE yourTable ALTER COLUMN col_name TYPE integer USING (col_name::integer);

Note that this will only work if you only have numbers in the column you are trying to convert. If you have anything else there, such as whitespace, you would need to remove it first before attempting the conversion.

Also, note that the conversion will also fail if any data be out of range for an integer type in Postgres (i.e. larger than 2147483647 or small than -2147483648).

Read here for a deeper discussion.