From: | Greg Sabino Mullane <htamfids(at)gmail(dot)com> |
---|---|
To: | Lok P <loknath(dot)73(at)gmail(dot)com> |
Cc: | sud <suds1434(at)gmail(dot)com>, pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Column type modification in big tables |
Date: | 2024-08-08 20:35:32 |
Message-ID: | CAKAnmmJ6fqyYafLB_im75oxxfTuCLUY0ftBPU57pUm0g+pm6FQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Thu, Aug 8, 2024 at 2:39 PM Lok P <loknath(dot)73(at)gmail(dot)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) <= 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
Cheers,
Greg
From | Date | Subject | |
---|---|---|---|
Next Message | Greg Sabino Mullane | 2024-08-08 20:45:18 | Re: Getting specific partition from the partition name |
Previous Message | veem v | 2024-08-08 19:52:36 | Getting specific partition from the partition name |