Lutz Horn <lutz@lutz-horn.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