Changing string(varchar) column to integer

Description

Environment
Adobe Campaign Classic
Adobe Campaign

Issue
A column in a table which although is currently of type string, it contains numbers and will always contains numbers.

Is there a way to change the type of column from varchar to int, and not lose all the numbers that are already there?

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

alter table myTable alter column vColumn int;

Resolution

Solutions

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):

  1. Edit the schema. Change the field type from string to long.
  2. During UPDATE DB STRUCTURE, replace the ALTER command with the below ALTER command, which changes the column type to INTEGER:

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

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

      

The Solution 2 is the best option.  It’s exactly how we handle 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, then you’re 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, it is recommended to create a backup table (with index) and time it:

select * into backup_nmsrecipient from nmsrecipient;

// add indexes to get accurate simulation

\timing

For example:

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

Usually this is done on custom tables that don’t block, so watch pg_stat_activity.

Likely if the nmsRecipient table is large, it will block during this operation.

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

On this page