public inbox for [email protected]
help / color / mirror / Atom feedFrom: Lok P <[email protected]>
To: Greg Sabino Mullane <[email protected]>
Cc: sud <[email protected]>
Cc: pgsql-general <[email protected]>
Subject: Re: Column type modification in big tables
Date: Fri, 9 Aug 2024 16:09:03 +0530
Message-ID: <CAKna9VZJ4fginFJZenGQxWs9eAw9Z8g-YkdnOFcie5RvuJ=5OQ@mail.gmail.com> (raw)
In-Reply-To: <CAKAnmmJ6fqyYafLB_im75oxxfTuCLUY0ftBPU57pUm0g+pm6FQ@mail.gmail.com>
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>
On Fri, Aug 9, 2024 at 2:06 AM Greg Sabino Mullane <[email protected]>
wrote:
> On Thu, Aug 8, 2024 at 2:39 PM Lok P <[email protected]> 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) <= 2)
> NOT VALID;
>
> > one of the columns from Number(10,2) to Numeric(8,2)
>
> ALTER TABLE foobar ADD CONSTRAINT eightprecision CHECK (mycol <= 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 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
>
>
>
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.
view thread (22+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: Column type modification in big tables
In-Reply-To: <CAKna9VZJ4fginFJZenGQxWs9eAw9Z8g-YkdnOFcie5RvuJ=5OQ@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox