Re: Column type modification in big tables
| От | Greg Sabino Mullane |
|---|---|
| Тема | Re: Column type modification in big tables |
| Дата | |
| Msg-id | CAKAnmmJ6fqyYafLB_im75oxxfTuCLUY0ftBPU57pUm0g+pm6FQ@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Column type modification in big tables (Lok P <loknath.73@gmail.com>) |
| Ответы |
Re: Column type modification in big tables
|
| Список | pgsql-general |
On Thu, Aug 8, 2024 at 2:39 PM 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) <= 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:
Cheers,
Greg
В списке pgsql-general по дате отправления: