Re: How to drop a NOT NULL column constraint? - Mailing list pgsql-general

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