> Can someone clue me in as to proper syntax for adding a not null
> check constraint? I've fumbled around a bit without much luck
> and I don't see this covered in the alter table section of the
> interactive docs.
>
> My pseudo code is:
> ALTER TABLE doof ADD CONSTRAINT bleibt_doof NOT NULL (record_id);
> But obviously this fails.
OK, Postgres doesn't currently have an SQL command for changing an
attributes NOT NULL property. There are a few options.
1) Add a CHECK constraint:
ATLER TABLE doof ADD CHECK (record_id IS NOT NULL);
This will work, but the column will still be of type 'null'.
2) Edit the catalogs
This isn't too hard:
UPDATE pg_attribute SET attnotnull = true WHERE attrelid = (SELECT oid FROM
pg_class WHERE relname = 'doof') AND attname = 'record_id';
You probably want to check that there's no NULL values in the column before
you do this. You can do it all in a transaction and LOCK the table as well
to make sure no-one adds NULL values while you're doing it.
3) I've attached two functions. Load them into your postgres. Just go:
SELECT kl_setnotnull('doof', 'record_id');
SELECT kl_setnull('doof', 'record_id');
These functions do all the proper locking and checking for you.
(These funcs are in the public domain BTW)
4) Wait for 7.3
The postgres CVS has a new command:
ALTER TABLE doof ALTER record_id SET NOT NULL;
ALTER TABLE doof ALTER record_id DROP NOT NULL;
FWIW, I recommend option (2) or (3) at the moment.
Cheers,
Chris