Adding a not null constraint faster

From: Joe Van Dyk <joe(at)tanga(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Adding a not null constraint faster
Date: 2014-05-17 08:39:27
Message-ID: CACfv+p+-mPNrz40FNxMFMTEDnsjb4o3XN8216xnogeSbhpr5hQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Is there a way to add a NOT NULL constraint to a column without having
to lock the table while a sequential read happens?

Seems like it should be possible to add an index on the column for
null values, like:
create index on t using btree(col_name) where col_name is null;

Then when adding the not null constraint, pg could lock the table, use
that index to see if there's any nulls, and if there's not, add the
constraint.

(That's not possible already, right? I couldn't figure it out if it was.)

Right now, the strategy for adding a not null constraint to a column
without any significant downtime involves creating a new table, as I
understand it.

Joe

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Thomas Kellerer 2014-05-17 12:25:00 9.4 beta - pg_get_viewdef() and WITH CHECK OPTION
Previous Message patrick keshishian 2014-05-17 04:50:02 Re: pg_query_params() problem with date_trunc() (possibly other functions as well)