Thread: ADD CONSTRAINT NOT NULL, how?

Hi all,

I'm trying to add a column with "NOT NULL" to an existing table using
the ALTER TABLE command.

db=> ALTER TABLE t1 ADD COLUMN f2 integer NOT NULL;
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. My problem
ist: what ist the syntax for adding this constraint?

BTW, im running "PostgreSQL 7.2.1 on i686-pc-linux-gnu".

Regards
Lutz

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