Changing string(varchar) column to integer

Description

For ACC, we have a column in a table which although is currently of type string, it contains numbers & will always contains numbers.

Is there a way to change the type of column from varchar to int & not lose all the numbers that are already in there.

For instance, is there a way to execute alter command (as below) or any other approach

alter table myTable alter column vColumn int;

Resolution

Solution 1 :-

1- Introduce a temp column of type int

2- Update the database

3- Create a workflow to copy the value from your original column into the new column
4- Change the type of the column and update the database

5- Create a workflow to copy the value from column introduce in step 1 to the original column

6- Delete the temp column and update the database

Solution 2 (Preferred one):-

  • Edit the schema. Change the field type from ‘string’ to ‘long’.
  • During UPDATE DB STRUCTURE, replace the ALTER command with the below ALTER command, which changes the column type to INT

ALTER TABLE NmsRecipient ALTER COLUMN sBoardingPass TYPE INTEGER USING sBoardingPass::integer;

https://www.postgresqltutorial.com/postgresql-change-column-type/

      

The 2nd way mentioned is the best way.  It’s exactly how we did (going to do) the ALTER TABLE for customers.

That ALTER TABLE is also a nice datatype validator…. If one of the rows has alpha data, the conversion (ALTER TABLE) will bounce…  If it succeeds, all set.

Schema similarly should be INT, not string and it causes all kinds of performance issues that can’t be solved even with 3-4 class RDS upsizing.

If you have space, I’d create a backup table (with index) and time it

select * into backup_nmsrecipient from nmsrecipient;

// add indexes to get accurate simulation

\timing

e.g.

ALTER TABLE backup_NmsRecipient ALTER COLUMN sBoardingPass TYPE INTEGER USING sBoardingPass::integer;

Usually, we do this on custom tables that don’t block… watch pg_stat_activity, likely if the nmsRecipient table is large, it will block during this operation.

PostGres has to rebuild the whole table (like a full vacuum)

On this page