MIME-Version: 1.0
From: veem v <veema0000@gmail.com>
Date: Thu, 9 Jan 2025 21:55:09 +0530
Message-ID: <CAB+=1TUcgyKXDKFS-G5GNjJHpP1cHyiXha1pAHc0HG5ZPPu9FQ@mail.gmail.com>
Subject: Alter table fast
To: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000d4bcd5062b486ced"
Archived-At: <https://www.postgresql.org/message-id/CAB%2B%3D1TUcgyKXDKFS-G5GNjJHpP1cHyiXha1pAHc0HG5ZPPu9FQ%40mail.gmail.com>
Precedence: bulk

--000000000000d4bcd5062b486ced
Content-Type: text/plain; charset="UTF-8"

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

--000000000000d4bcd5062b486ced
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hello, <br>It&#39;s postgres version 16.1, we want to conv=
ert an existing column data type from integer to numeric and it&#39;s takin=
g a long time. The size of the table is ~50GB and the table has ~150million=
 rows in it and it&#39;s not partitioned. We tried running the direct alter=
 and it&#39;s going beyond hours, so wanted to understand from experts what=
 is the best way to achieve this?<br><br>1)Should we go with below <br>Alte=
r table &lt;table_name&gt; alter column &lt;column_name&gt; type numeric(15=
,0) USING &lt;column_name&gt;::NUMERIC(15,0);<br>OR<br>We should add a new =
not null column.<br>update the data in that column from the existing column=
.<br>drop the old column <br>rename the new column to the old column.<br><d=
iv><br></div><div>Regards</div><div>Veem</div></div>

--000000000000d4bcd5062b486ced--