Re: Is it possible to speed up addition of "not null"?

On Thu, Feb 02, 2012 at 12:48:04PM +0100, hubert depesz lubaczewski
wrote
> I need to add not null on one of the columns, but it seems to require
> full table scan.

Of course it does. If you want a constraint added to the table, the
first thing it ought to do is check that all your data actually
matches the constraint. If not, your constraint doesn't work.

> I tried with some indexes, but I can't get the time to something
> reasonable, so here is my question: is there any way I could make the
> "not null" constraint *fast*?

Not faster than a table scan, no. How fast do you want, though? It
doesn't sound like an unreasonably large table. Have you done any
tuning? Do you have adequate hardware?

Maybe faster would be to create a new table with the schema you want,
and then use COPY to pull the data out of the old table and into the
new table. (It sounds like what you really want is a primary key,
however, and that's going to be faster if you build the unique index
after the data's all loaded.

A

--
Andrew Sullivan
ajs(at)crankycanuck(dot)ca