MIME-Version: 1.0
References: <CAKna9VaJ_qHKBnw4O-VT3xGmzqThCuZ=LFXx-hPdw7E6RoqmeA@mail.gmail.com>
 <CAD=mzVUmXmkdvvMG30G1=D4Kq3WqnzGo=0ov9JnRCs1p=KJiTQ@mail.gmail.com>
 <CAKna9VZRc4+Vzbt6qPGMCauE84isPtz-wE_KX9AOt7WKfhwjiQ@mail.gmail.com>
 <CAD=mzVUX13ZM16kP4QhY+F5XiLr=ezCXftKOTKA4eUvhphgOJw@mail.gmail.com>
 <CAKna9Vb_mx+dX02XOV6mpr8RFC-5io38kM6=4xRHQj_MUvQ+aQ@mail.gmail.com> <CAKAnmmJ6fqyYafLB_im75oxxfTuCLUY0ftBPU57pUm0g+pm6FQ@mail.gmail.com>
In-Reply-To: <CAKAnmmJ6fqyYafLB_im75oxxfTuCLUY0ftBPU57pUm0g+pm6FQ@mail.gmail.com>
From: Lok P <loknath.73@gmail.com>
Date: Fri, 9 Aug 2024 16:09:03 +0530
Message-ID: <CAKna9VZJ4fginFJZenGQxWs9eAw9Z8g-YkdnOFcie5RvuJ=5OQ@mail.gmail.com>
Subject: Re: Column type modification in big tables
To: Greg Sabino Mullane <htamfids@gmail.com>
Cc: sud <suds1434@gmail.com>, pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000002c5f89061f3dc148"
Archived-At: <https://www.postgresql.org/message-id/CAKna9VZJ4fginFJZenGQxWs9eAw9Z8g-YkdnOFcie5RvuJ%3D5OQ%40mail.gmail.com>
Precedence: bulk

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

On Fri, Aug 9, 2024 at 2:06=E2=80=AFAM Greg Sabino Mullane <htamfids@gmail.=
com>
wrote:

> On Thu, Aug 8, 2024 at 2:39=E2=80=AFPM Lok P <loknath.73@gmail.com> wrote=
:
>
>> Can anybody suggest any other possible way here.
>>
>
> Sure - how about not changing the column type at all?
>
> > one of the columns from varchar(20) to varchar(2)
>
> ALTER TABLE foobar ADD CONSTRAINT twocharplease CHECK (length(mycol) <=3D=
 2)
> NOT VALID;
>
> > one of the columns from Number(10,2) to Numeric(8,2)
>
> ALTER TABLE foobar ADD CONSTRAINT eightprecision CHECK (mycol <=3D 10^8) =
NOT
> VALID;
>
> > two of the columns from varchar(20) to numeric(3)
>
> This one is trickier, as we don't know the contents, nor why it is going
> to numeric(3) - not a terribly useful data type, but let's roll with it a=
nd
> assume the stuff in the varchar is a number of some sort, and that we don=
't
> allow nulls:
>
> ALTER TABLE foobar ADD CONSTRAINT onekorless CHECK (mycol::numeric(3) is
> not null) NOT VALID;
>
> You probably want to check on the validity of the existing rows: see the
> docs on VALIDATE CONSTRAINT here:
>
> https://www.postgresql.org/docs/current/sql-altertable.html
>
>
>
Thank you so much. Will definitely try to evaluate this approach. The Only
concern I have is , as this data is moving downstream with exactly the same
data type and length , so will it cause the downstream code to break while
using this column in the join or filter criteria. Also I believe the
optimizer won't be able to utilize this information while preparing the
execution plan.

 Another thing , correct me if wrong, My understanding is  , if we want to
run the "validate constraint" command after running this "check constraint
with not valid" command, this will do a full table scan across all the
partitions , but it's still beneficial as compared to updating the columns
values for each rows. Correct me if I'm wrong.

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

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><br><div class=3D"gmail_quote">=
<div dir=3D"ltr" class=3D"gmail_attr">On Fri, Aug 9, 2024 at 2:06=E2=80=AFA=
M Greg Sabino Mullane &lt;<a href=3D"mailto:htamfids@gmail.com">htamfids@gm=
ail.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"=
margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-lef=
t:1ex"><div dir=3D"ltr"><div dir=3D"ltr">On Thu, Aug 8, 2024 at 2:39=E2=80=
=AFPM Lok P &lt;<a href=3D"mailto:loknath.73@gmail.com" target=3D"_blank">l=
oknath.73@gmail.com</a>&gt; wrote:</div><div class=3D"gmail_quote"><blockqu=
ote class=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 class=3D"gm=
ail_quote"><div>Can anybody suggest any other possible way here.</div></div=
></div></blockquote><div><br></div><div><span style=3D"color:rgb(80,0,80)">=
Sure - how about not changing the column type at all?</span></div><div><spa=
n style=3D"color:rgb(80,0,80)"><br></span></div><div><span style=3D"color:r=
gb(80,0,80)">&gt; one of the columns from varchar(20) to varchar(2)</span><=
br></div><div><span style=3D"color:rgb(80,0,80)"><br></span></div><div><spa=
n style=3D"color:rgb(80,0,80)">ALTER TABLE foobar ADD CONSTRAINT twocharple=
ase CHECK (length(mycol) &lt;=3D 2) NOT VALID;</span></div><div><span style=
=3D"color:rgb(80,0,80)"><br></span></div><div><span style=3D"color:rgb(80,0=
,80)">&gt; one of the columns from Number(10,2) to Numeric(8,2)</span></div=
><div><br></div><div>ALTER TABLE foobar ADD CONSTRAINT eightprecision CHECK=
 (mycol &lt;=3D 10^8) NOT VALID;</div><div><br></div><div>&gt;=C2=A0<span s=
tyle=3D"color:rgb(80,0,80)">two of the columns from varchar(20) to numeric(=
3)</span></div><div><br></div><div>This one is trickier, as we don&#39;t kn=
ow the contents, nor why it is going to numeric(3) - not a terribly useful =
data type, but let&#39;s roll with it and assume the stuff in the varchar i=
s a number of some sort, and that we don&#39;t allow nulls:</div><div><br><=
/div><div>ALTER TABLE foobar ADD CONSTRAINT onekorless CHECK (mycol::numeri=
c(3) is not null) NOT VALID;</div><div><br></div><div>You probably want to =
check on the validity of the existing rows: see the docs on VALIDATE CONSTR=
AINT here:</div><div><br></div><div><a href=3D"https://www.postgresql.org/d=
ocs/current/sql-altertable.html" target=3D"_blank">https://www.postgresql.o=
rg/docs/current/sql-altertable.html</a><br></div><div><br></div><div><br></=
div></div></div></blockquote><div><br></div><div>Thank you so much. Will de=
finitely try to evaluate this approach. The Only concern I have is , as thi=
s data is moving downstream with exactly the same data type and length , so=
 will it cause the downstream code to break while using this column in the =
join or filter criteria. Also I believe the optimizer won&#39;t be able to =
utilize this information while preparing the execution plan.</div><div><br>=
</div><div>=C2=A0Another thing , correct me if wrong, My understanding is=
=C2=A0 , if we want to run the &quot;validate constraint&quot; command afte=
r running this &quot;check constraint with not valid&quot; command, this wi=
ll do a full table scan across all the partitions , but it&#39;s=C2=A0still=
 beneficial as compared to updating the columns values for each rows. Corre=
ct me if I&#39;m wrong.=C2=A0</div><div><br></div><div><br></div><div><br><=
/div></div></div>

--0000000000002c5f89061f3dc148--