MIME-Version: 1.0
References: <CAB+=1TUcgyKXDKFS-G5GNjJHpP1cHyiXha1pAHc0HG5ZPPu9FQ@mail.gmail.com>
 <CANzqJaB-k8Sae4st0NP4BW_VuNRP6e1V=f0E7O28URR23MuEww@mail.gmail.com>
In-Reply-To: <CANzqJaB-k8Sae4st0NP4BW_VuNRP6e1V=f0E7O28URR23MuEww@mail.gmail.com>
From: Gus Spier <gus.spier@gmail.com>
Date: Mon, 27 Jan 2025 21:29:24 -0500
Message-ID: <CAG8xniep8Ew_aTQiozWMDF_wpaQ_d3En1HNmanuPzg0OSncBqg@mail.gmail.com>
Subject: Re: Alter table fast
To: Ron Johnson <ronljohnsonjr@gmail.com>
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000bd4a92062cbaf64d"
Archived-At: <https://www.postgresql.org/message-id/CAG8xniep8Ew_aTQiozWMDF_wpaQ_d3En1HNmanuPzg0OSncBqg%40mail.gmail.com>
Precedence: bulk

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

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=E2=80=AFAM Ron Johnson <ronljohnsonjr@gmail.co=
m> wrote:

> On Thu, Jan 9, 2025 at 11:25=E2=80=AFAM veem v <veema0000@gmail.com> wrot=
e:
>
>> 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 th=
e
>> 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 hou=
rs,
>> so wanted to understand from experts what is the best way to achieve thi=
s?
>>
>>
> 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!
>

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

<div dir=3D"ltr">I don&#39;t think I qualify as an expert, but I can nomina=
te a course of action for you to consider.<div><br></div><div>Rather than c=
onvert an existing column from one data type to another, might it not be ea=
sier to simply recreate the table with the correct data type.</div><div>The=
re are=C2=A0 caveats!=C2=A0</div><div><br></div><div>You have to consider a=
ny referential integrity constraints.</div><div>You have to have a LOT of a=
vailable disk space.</div><div>You really want to do this in batches.</div>=
<div><br></div><div>If you can swing those issues, you might have a shot.</=
div><div><br></div><div>=C2=A01. CREATE TABLE &lt;table_name_new&gt; LIKE &=
lt;original_table_name&gt;;</div><div>2. ALTER &lt;table_name_new&gt; ALTER=
 COLUMN &lt;column_name&gt; TYPE &lt;new_data_type&gt;;</div><div>3. INSERT=
 INTO &lt;table_name_new&gt; SELECT &lt;column list w. the target column_na=
me cast as the new type&gt;</div><div>-- recommend you do this in batches</=
div><div>4. DROP TABLE &lt;original_table_name&gt;;</div><div>5. ALTER TABL=
E &lt;table_name_new&gt; RENAME to &lt;original_table_name&gt;</div><div><b=
r></div><div>Regards,</div><div>Gus</div></div><br><div class=3D"gmail_quot=
e gmail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">On Thu, Jan =
9, 2025 at 11:27=E2=80=AFAM Ron Johnson &lt;<a href=3D"mailto:ronljohnsonjr=
@gmail.com">ronljohnsonjr@gmail.com</a>&gt; wrote:<br></div><blockquote cla=
ss=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid =
rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div dir=3D"ltr">On Thu=
, Jan 9, 2025 at 11:25=E2=80=AFAM veem v &lt;<a href=3D"mailto:veema0000@gm=
ail.com" target=3D"_blank">veema0000@gmail.com</a>&gt; wrote:</div><div cla=
ss=3D"gmail_quote"><blockquote class=3D"gmail_quote" style=3D"margin:0px 0p=
x 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div d=
ir=3D"ltr">Hello, <br>It&#39;s postgres version 16.1, we want to convert an=
 existing column data type from integer to numeric and it&#39;s taking a lo=
ng 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 i=
t&#39;s going beyond hours, so wanted to understand from experts what is th=
e best way to achieve this?<br></div><div dir=3D"ltr"><br></div></blockquot=
e><div><br></div><div>Out of curiosity, why NUMERIC(15,0) instead of BIGINT=
?=C2=A0</div></div><div><br></div><span class=3D"gmail_signature_prefix">--=
 </span><br><div dir=3D"ltr" class=3D"gmail_signature"><div dir=3D"ltr">Dea=
th to &lt;Redacted&gt;, and butter sauce.<div>Don&#39;t boil me, I&#39;m st=
ill alive.<br><div><div>&lt;Redacted&gt; lobster!</div></div></div></div></=
div></div>
</blockquote></div>

--000000000000bd4a92062cbaf64d--