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

Would it help to create a new not null column in the target table, and then update the table by copying values from old
columnto the new, not null column? Of course you’d have to ignore errors, etc. but wouldn’t that perform at enough for
yourneeds? 

Sent from my iPhone

> On Sep 8, 2021, at 1:15 AM, hubert depesz lubaczewski <depesz@depesz.com> wrote:
>
> On Wed, Sep 08, 2021 at 07:09:31AM +0200, Alexander Kukushkin wrote:
>> 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.
>
> Thanks. Forgot about these.
>
> Best regards,
>
> depesz
>
>
>