Re: Alter table fast

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

On Thu, Jan 9, 2025 at 11:27 AM Ron Johnson <ronljohnsonjr(at)gmail(dot)com> wrote:

> On Thu, Jan 9, 2025 at 11:25 AM veem v <veema0000(at)gmail(dot)com> wrote:
>
>> 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.
> <Redacted> lobster!
>