Postgres Pro
Downloads
Home   >   mailing lists

Re: Is there any technical reason why "alter table .. set not null" can't use index? - Mailing list pgsql-general

From Alexander Kukushkin
Subject Re: Is there any technical reason why "alter table .. set not null" can't use index?
Date
Msg-id CAFh8B==Jqo2L1u53oeSp5o6c-d1Wx0_qejBQ6DjZ5agZ-3750g@mail.gmail.com
Whole thread Raw
In response to Is there any technical reason why "alter table .. set not null" can't use index?  (hubert depesz lubaczewski <depesz@depesz.com>)
Responses Re: Is there any technical reason why "alter table .. set not null" can't use index?  (hubert depesz lubaczewski <depesz@depesz.com>)
List pgsql-general
Tree view
Hi,

On Wed, 8 Sep 2021, 06:59 hubert depesz lubaczewski, <depesz@depesz.com> wrote:
Hi,
we needed recently to add not null constraint on some fields, and it
struck me that it took long.
Obviously - pg has to check the data. But it seems that it can't use
index.

It can't use the index, but can use an already existing CHECK CONSTRAINT, that could be created as NOT VALID and validated without holding heavy locks. After adding not null you can drop the constraint.


Regards,
--
Alexander Kukushkin

pgsql-general by date:

Previous
From: hubert depesz lubaczewski
Date:
Subject: Is there any technical reason why "alter table .. set not null" can't use index?
Next
From: hubert depesz lubaczewski
Date:
Subject: Re: Is there any technical reason why "alter table .. set not null" can't use index?