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;
Solutions
Solution 1:
int
.Solution 2 (Preferred):
string
to long
.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).