This might be helpful..
--- Begin Message ---> 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
kl_setnotnull.sql
Description: Binary data
Description: Binary data---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly--- End Message ---
signature.asc
Description: This is a digitally signed message part
