Changing string(varchar) column to integer
A quick guide to change string(varchar) column to an integer by changing the field type from string
to long
.
Description description
Environment
- Adobe Campaign Classic
- Adobe Campaign
Issue/Symptoms
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 resolution
Solutions
Solution 1:
- Introduce a temp column of type
int
. - Update the database.
- Create a workflow to copy the value from your original column into the new column.
- Change the type of the column and update the database.
- Create a workflow to copy the value from column introduce in Step 1 to the original column.
- Delete the temp column and update the database.
Solution 2 (Preferred):
- Edit the schema. Change the field type from
string
tolong
. - During
UPDATE DB STRUCTURE
, replace theALTER
command with the belowALTER
command, which changes the column type toINTEGER
:
ALTER TABLE NmsRecipient ALTER COLUMN sBoardingPass TYPE INTEGER USING sBoardingPass::integer;
https://www.postgresqltutorial.com/postgresql-change-column-type/
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).