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

From: Alexander Kukushkin <cyberdemn(at)gmail(dot)com>
To: depesz(at)depesz(dot)com
Cc: pgsql-general mailing list <pgsql-general(at)postgresql(dot)org>
Subject: Re: Is there any technical reason why "alter table .. set not null" can't use index?
Date: 2021-09-08 05:09:31
Message-ID: CAFh8B==Jqo2L1u53oeSp5o6c-d1Wx0_qejBQ6DjZ5agZ-3750g@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

On Wed, 8 Sep 2021, 06:59 hubert depesz lubaczewski, <depesz(at)depesz(dot)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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message hubert depesz lubaczewski 2021-09-08 05:14:53 Re: Is there any technical reason why "alter table .. set not null" can't use index?
Previous Message hubert depesz lubaczewski 2021-09-08 04:59:19 Is there any technical reason why "alter table .. set not null" can't use index?