Re: CHECK CONSTRAINT blunder(s)

> 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