Alter table fast

From: veem v <veema0000(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Alter table fast
Date: 2025-01-09 16:25:09
Message-ID: CAB+=1TUcgyKXDKFS-G5GNjJHpP1cHyiXha1pAHc0HG5ZPPu9FQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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?

1)Should we go with below
Alter table <table_name> alter column <column_name> type numeric(15,0)
USING <column_name>::NUMERIC(15,0);
OR
We should add a new not null column.
update the data in that column from the existing column.
drop the old column
rename the new column to the old column.

Regards
Veem

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ron Johnson 2025-01-09 16:27:20 Re: Alter table fast
Previous Message vijay patil 2025-01-09 07:36:29 Re: Issue with Password Authentication for Pgpool