MIME-Version: 1.0
References: <CAB+=1TUcgyKXDKFS-G5GNjJHpP1cHyiXha1pAHc0HG5ZPPu9FQ@mail.gmail.com>
 <CANzqJaB-k8Sae4st0NP4BW_VuNRP6e1V=f0E7O28URR23MuEww@mail.gmail.com>
 <CAB+=1TVSEZB6XSxzAYSp1O91i3JKmMRgPjckLT0Dokrfd_PxWQ@mail.gmail.com>
 <9e3d705e-df1d-4d32-b5ba-718f492cb1cc@gmx.net> <20250112211901.r6cealpdlsovwahv@hjp.at>
In-Reply-To: <20250112211901.r6cealpdlsovwahv@hjp.at>
From: Marco Torres <mtors25@gmail.com>
Date: Sun, 12 Jan 2025 14:36:10 -0700
Message-ID: <CAG2LZV58fabgd-nGWe=XkkyrsEb9+zo3rUi_oybuhDh_QV5JBQ@mail.gmail.com>
Subject: Re: Alter table fast
To: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000007e3505062b891e34"
Archived-At: <https://www.postgresql.org/message-id/CAG2LZV58fabgd-nGWe%3DXkkyrsEb9%2Bzo3rUi_oybuhDh_QV5JBQ%40mail.gmail.com>
Precedence: bulk

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

This is the right approach, Peter J. Holzer, from a well season DBA
perspective "ALTER TABLE working_table
ADD COLUMN B INTEGER ; UPDATE working_table
SET B =3D A;"

Bare in mind the indexes or existing references to an from other tables and
act accordingly-- define the new and drop the old.

Good luck.

On Sun, Jan 12, 2025, 2:20=E2=80=AFPM Peter J. Holzer <hjp-pgsql@hjp.at> wr=
ote:

> On 2025-01-09 20:52:27 +0100, shammat@gmx.net wrote:
> > Am 09.01.25 um 20:17 schrieb veem v:
> > > > Out of curiosity, why NUMERIC(15,0) instead of BIGINT?
> > >
> > > It's for aligning the database column types to the data model and
> > > it's happening across all the upstream downstream systems. I was
> > > thinking if this can be made  faster with the single line alter
> > > statement "Alter table <table_name> alter column <column_name> type
> > > numeric(15,0) USING <column_name>::NUMERIC(15,0);"
> >
> > Hmm, I would rather change numeric(15,0) to bigint if I had to "align"
> types across systems.
>
> I'm also wondering what "the data model" is.
>
> If I have numeric(15,0) in an abstract data model, that means that I
> expect values larger than 99,999,999,999,999 but at most
> 999,999,999,999,999. That seems to be oddly specific and also somewhat
> at odds with reality when until now there apparently haven't been any
> values larger than 2,147,483,647. What kind of real world value could
> suddenly jump by more than 5 orders of magnitude but certainly not by 7?
>
> A bigint is much less precise (more than 2,147,483,647 but not more
> than 9,223,372,036,854,775,807) and therefore more suitable for values
> where you don't really know the range.
>
> However, for the problem at hand, I doubt it makes any difference.
> Surely converting a few million values takes much less time than
> rewriting a 50 GB table and all its indexes.
>
> So there isn't really a faster way to do what Veem wants. There may
> however be less disruptive way: He could create a new column with the
> new values (which takes at least as long but can be done in the
> background) and then switch it over and drop the old column.
>
>         hp
>
> --
>    _  | Peter J. Holzer    | Story must make more sense than reality.
> |_|_) |                    |
> | |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
> __/   | http://www.hjp.at/ |       challenge!"
>

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

<div dir=3D"auto"><div>This is the right approach, Peter J. Holzer, from a =
well season DBA perspective &quot;ALTER TABLE working_table</div><div dir=
=3D"auto">ADD COLUMN B INTEGER=C2=A0; UPDATE working_table</div><div dir=3D=
"auto">SET B =3D A;&quot;</div><div dir=3D"auto"><br></div><div dir=3D"auto=
">Bare in mind the indexes or existing references to an from other tables a=
nd act accordingly-- define the new and drop the old.</div><div dir=3D"auto=
"><br></div><div dir=3D"auto">Good luck.</div><div dir=3D"auto"><br><div cl=
ass=3D"gmail_quote gmail_quote_container" dir=3D"auto"><div dir=3D"ltr" cla=
ss=3D"gmail_attr">On Sun, Jan 12, 2025, 2:20=E2=80=AFPM Peter J. Holzer &lt=
;<a href=3D"mailto:hjp-pgsql@hjp.at">hjp-pgsql@hjp.at</a>&gt; wrote:<br></d=
iv><blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left=
:1px #ccc solid;padding-left:1ex">On 2025-01-09 20:52:27 +0100, <a href=3D"=
mailto:shammat@gmx.net" target=3D"_blank" rel=3D"noreferrer">shammat@gmx.ne=
t</a> wrote:<br>
&gt; Am 09.01.25 um 20:17 schrieb veem v:<br>
&gt; &gt; &gt; Out of curiosity, why NUMERIC(15,0) instead of BIGINT?<br>
&gt; &gt; <br>
&gt; &gt; It&#39;s for aligning the database column types to the data model=
 and<br>
&gt; &gt; it&#39;s happening across all the upstream downstream systems. I =
was<br>
&gt; &gt; thinking if this can be made=C2=A0 faster with the single line al=
ter<br>
&gt; &gt; statement &quot;Alter table &lt;table_name&gt; alter column &lt;c=
olumn_name&gt; type<br>
&gt; &gt; numeric(15,0) USING &lt;column_name&gt;::NUMERIC(15,0);&quot;<br>
&gt; <br>
&gt; Hmm, I would rather change numeric(15,0) to bigint if I had to &quot;a=
lign&quot; types across systems.<br>
<br>
I&#39;m also wondering what &quot;the data model&quot; is.<br>
<br>
If I have numeric(15,0) in an abstract data model, that means that I<br>
expect values larger than 99,999,999,999,999 but at most<br>
999,999,999,999,999. That seems to be oddly specific and also somewhat<br>
at odds with reality when until now there apparently haven&#39;t been any<b=
r>
values larger than 2,147,483,647. What kind of real world value could<br>
suddenly jump by more than 5 orders of magnitude but certainly not by 7?<br=
>
<br>
A bigint is much less precise (more than 2,147,483,647 but not more<br>
than 9,223,372,036,854,775,807) and therefore more suitable for values<br>
where you don&#39;t really know the range.<br>
<br>
However, for the problem at hand, I doubt it makes any difference.<br>
Surely converting a few million values takes much less time than<br>
rewriting a 50 GB table and all its indexes.<br>
<br>
So there isn&#39;t really a faster way to do what Veem wants. There may<br>
however be less disruptive way: He could create a new column with the<br>
new values (which takes at least as long but can be done in the<br>
background) and then switch it over and drop the old column.<br>
<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 hp<br>
<br>
-- <br>
=C2=A0 =C2=A0_=C2=A0 | Peter J. Holzer=C2=A0 =C2=A0 | Story must make more =
sense than reality.<br>
|_|_) |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 |<br>
| |=C2=A0 =C2=A0| <a href=3D"mailto:hjp@hjp.at" target=3D"_blank" rel=3D"no=
referrer">hjp@hjp.at</a>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|=C2=A0 =C2=A0 --=
 Charles Stross, &quot;Creative writing<br>
__/=C2=A0 =C2=A0| <a href=3D"http://www.hjp.at/" rel=3D"noreferrer noreferr=
er" target=3D"_blank">http://www.hjp.at/</a> |=C2=A0 =C2=A0 =C2=A0 =C2=A0ch=
allenge!&quot;<br>
</blockquote></div></div></div>

--0000000000007e3505062b891e34--