i've always had to add the column first and then do: update pg_attribute set attnotnull = 't' where attname = 'fieldname' and attrelid = (select oid from pg_class where relname = 'tablename'); but i'm using version 7.0.2, maybe the alter column to set not null works in later versions? tamsin -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Gregory Wood Sent: 26 June 2001 20:11 To: Philip Molter Cc: PostgreSQL-General Subject: [GENERAL] Re: Adding a NOT NULL column? > but how do set that new column to be NOT NULL? The postgres docs say > do something like: > > ALTER TABLE t ADD (table constraint definition) > > so I would do that like: > > ALTER TABLE ADD c NOT NULL > ALTER TABLE ADD CONSTRAINT c NOT NULL > > each time it fails on the 'NOT'. How do I add that constraint? I believe you can either ALTER the column (much like you did when you added the default value: ALTER TABLE t ALTER c SET DEFAULT 0), or ADD a CHECK constraint which checks that 'c IS NOT NULL'. I don't think you can simply *add* a 'NOT NULL' constraint to a column after the fact. Both of these should work fine, although I'm more comfortable with the syntax of the first: ALTER TABLE ALTER COLUMN c NOT NULL; -OR- ALTER TABLE ADD CONSTRAINT CHECK (c IS NOT NULL); Greg ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster