Re: ALTER TABLE for field modify... [Fwd: Re: [SQL] CHECK

This might be helpful..
> 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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly