I don't think I qualify as an expert, but I can nominate a course of action for you to consider.
Rather than convert an existing column from one data type to another, might it not be easier to simply recreate the table with the correct data type.
There are caveats!
You have to consider any referential integrity constraints.
You have to have a LOT of available disk space.
You really want to do this in batches.
If you can swing those issues, you might have a shot.
1. CREATE TABLE <table_name_new> LIKE <original_table_name>;
2. ALTER <table_name_new> ALTER COLUMN <column_name> TYPE <new_data_type>;
3. INSERT INTO <table_name_new> SELECT <column list w. the target column_name cast as the new type>
-- recommend you do this in batches
4. DROP TABLE <original_table_name>;
5. ALTER TABLE <table_name_new> RENAME to <original_table_name>
Regards,
Gus
Hello,
It's postgres version 16.1, we want to convert an existing column data type from integer to numeric and it's taking a long time. The size of the table is ~50GB and the table has ~150million rows in it and it's not partitioned. We tried running the direct alter and it's going beyond hours, so wanted to understand from experts what is the best way to achieve this?
Out of curiosity, why NUMERIC(15,0) instead of BIGINT?
-- Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
