How to drop a NOT NULL column constraint?

On Tue, 2 Jan 2001, Ed Loehr wrote:

I have a table created like so:

CREATE TABLE foo (
id INTEGER NOT NULL
...
);

I need to alter the table to get rid of the "NOT NULL" constraint on the
'id' column. Can I do this using ALTER TABLE? Or do I have to
dump/recreate/reload the table?

I'm looking for something like "ALTER TABLE foo ALTER COLUMN id DROP NOT
NULL", but the docs don't hint at it...

I don't think Postgres supports DROP CONSTRAINT in the ALTER TABLE
statement yet. You'll need to create a temp table with the same structure
but without the constaint and do a SELECT INTO to copy the data over, then
delete the old table and rename the new table to the old name.

-- Brett
http://www.chapelperilous.net/~bmccoy/
---------------------------------------------------------------------------
Shah, shah! Ayatollah you so!

Ed Loehr <eloehr@austin.rr.com> writes:

I need to alter the table to get rid of the "NOT NULL" constraint on the
'id' column. Can I do this using ALTER TABLE?

There isn't an ALTER TABLE variant for this at the moment, but you can
do it the hard way: reach in and change the attnotnull boolean in the
column's pg_attribute row. The actual update would only require

UPDATE pg_attribute SET attnotnull = 'f' WHERE
attname = 'id' AND attrelid = whatever...

but I don't believe this will be noticed automatically by running
backends. I think a VACUUM on your table afterwards would be sufficient
to force the backends to notice the change.

regards, tom lane

Tom Lane wrote:

Ed Loehr <eloehr@austin.rr.com> writes:

I need to alter the table to get rid of the "NOT NULL" constraint on the
'id' column. Can I do this using ALTER TABLE?

There isn't an ALTER TABLE variant for this at the moment, but you can
do it the hard way: reach in and change the attnotnull boolean in the
column's pg_attribute row. The actual update would only require

UPDATE pg_attribute SET attnotnull = 'f' WHERE
attname = 'id' AND attrelid = whatever...

but I don't believe this will be noticed automatically by running
backends. I think a VACUUM on your table afterwards would be sufficient
to force the backends to notice the change.

Thanks. That's nice and easy. For posterity, here's the command I used:

UPDATE pg_attribute SET attnotnull = 'f'
FROM pg_class c
WHERE attname = 'id'
AND attrelid = c.oid
AND c.relkind = 'r'
AND c.relname = 'foo';

(not sure the 'relkind' predicate is necessary).

Regards,
Ed Loehr