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>
In-Reply-To: <CAKna9Vb_mx+dX02XOV6mpr8RFC-5io38kM6=4xRHQj_MUvQ+aQ@mail.gmail.com>
From: Greg Sabino Mullane <htamfids@gmail.com>
Date: Thu, 8 Aug 2024 16:35:32 -0400
Message-ID: <CAKAnmmJ6fqyYafLB_im75oxxfTuCLUY0ftBPU57pUm0g+pm6FQ@mail.gmail.com>
Subject: Re: Column type modification in big tables
To: Lok P <loknath.73@gmail.com>
Cc: sud <suds1434@gmail.com>, pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000020ec63061f31fa2b"
Archived-At: <https://www.postgresql.org/message-id/CAKAnmmJ6fqyYafLB_im75oxxfTuCLUY0ftBPU57pUm0g%2Bpm6FQ%40mail.gmail.com>
Precedence: bulk

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

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) NO=
T
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 and
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

Cheers,
Greg

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

<div dir=3D"ltr"><div dir=3D"ltr">On Thu, Aug 8, 2024 at 2:39=E2=80=AFPM Lo=
k P &lt;<a href=3D"mailto:loknath.73@gmail.com">loknath.73@gmail.com</a>&gt=
; wrote:</div><div class=3D"gmail_quote"><blockquote class=3D"gmail_quote" =
style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);pa=
dding-left:1ex"><div dir=3D"ltr"><div class=3D"gmail_quote"><div>Can anybod=
y 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 chang=
ing the column type at all?</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 t=
he columns from varchar(20) to varchar(2)</span><br></div><div><span style=
=3D"color:rgb(80,0,80)"><br></span></div><div><span style=3D"color:rgb(80,0=
,80)">ALTER TABLE foobar ADD CONSTRAINT twocharplease 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 col=
umns from Number(10,2) to Numeric(8,2)</span></div><div><br class=3D"gmail-=
Apple-interchange-newline"></div><div>ALTER TABLE foobar ADD CONSTRAINT eig=
htprecision CHECK (mycol &lt;=3D 10^8) NOT VALID;</div><div><br></div><div>=
&gt;=C2=A0<span style=3D"color:rgb(80,0,80)">two of the columns from varcha=
r(20) to numeric(3)</span></div><div><br></div><div>This one is trickier, a=
s we don&#39;t know 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 is a number of some sort, and that we don&#39;t allow nulls=
:</div><div><br></div><div>ALTER TABLE foobar ADD CONSTRAINT onekorless CHE=
CK (mycol::numeric(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 o=
n VALIDATE CONSTRAINT here:</div><div><br></div><div><a href=3D"https://www=
.postgresql.org/docs/current/sql-altertable.html">https://www.postgresql.or=
g/docs/current/sql-altertable.html</a><br></div><div><br></div><div>Cheers,=
</div><div>Greg</div><div><br></div><blockquote 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"gmail_quote"><blockquote class=3D=
"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(2=
04,204,204);padding-left:1ex">
</blockquote></div></div>
</blockquote></div></div>

--00000000000020ec63061f31fa2b--