Re: ADD CONSTRAINT NOT NULL, how?

Lutz Horn <lutz(at)lutz-horn(dot)de> writes:
> ERROR: Adding NOT NULL columns is not implemented.
> Add the column, then use ALTER TABLE ADD CONSTRAINT.

> OK, this is not the way to do it. The solution seems to be to first add
> the column without "NOT NULL" and use "ADD CONSTRAINT" later.

Actually three steps: ALTER ADD COLUMN, do an UPDATE to fill the column
with non-null in all existing rows, and then finally you can add the
constraint.

> My problem ist: what ist the syntax for adding this constraint?

Right now you have to do it as a generic CHECK condition:
CHECK (col IS NOT NULL)
This is sort of annoying because it's a shade less efficient than
the native NOT NULL constraint. If you are worried about that,
you could cheat: manually set the attisnotnull field to 'true' in
the new column's pg_attribute row. (If you do this, it's up to you
to be sure all the extant rows have non-null values first.)

7.3 will have a direct ALTER TABLE ... SET NOT NULL command that
tests the existing rows and then sets attisnotnull.

regards, tom lane