| From: | Gus Spier <gus(dot)spier(at)gmail(dot)com> |
|---|---|
| To: | depesz(at)depesz(dot)com |
| Cc: | Alexander Kukushkin <cyberdemn(at)gmail(dot)com>, 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-10 17:26:46 |
| Message-ID: | 7E44EA26-9343-444F-B01C-30944AB8C60A@gmail.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
Would it help to create a new not null column in the target table, and then update the table by copying values from old column to the new, not null column? Of course you’d have to ignore errors, etc. but wouldn’t that perform at enough for your needs?
Sent from my iPhone
> On Sep 8, 2021, at 1:15 AM, hubert depesz lubaczewski <depesz(at)depesz(dot)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(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.
>
> Thanks. Forgot about these.
>
> Best regards,
>
> depesz
>
>
>
| From | Date | Subject | |
|---|---|---|---|
| Next Message | hubert depesz lubaczewski | 2021-09-10 17:55:52 | Re: Is there any technical reason why "alter table .. set not null" can't use index? |
| Previous Message | Richard Michael | 2021-09-10 12:38:18 | Re: How to observe plan_cache_mode transition from custom to generic plan? |