Re: [GENERAL] How to drop a NOT NULL column constraint?

2001-01-02 Thread Ed Loehr 

Tom Lane wrote:
> 
> Ed Loehr <[EMAIL PROTECTED]> 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

Re: [GENERAL] How to drop a NOT NULL column constraint?

2001-01-02 Thread Tom Lane 

Ed Loehr <[EMAIL PROTECTED]> 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

Re: [GENERAL] How to drop a NOT NULL column constraint?

2001-01-02 Thread Brett W. McCoy 

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!