Removing all NOT NULL constraints from multiple tables easily

Yes, but you'll probably not like it... You can modify the
system tables to remove the not nulls. We don't yet have
the ALTER TABLE syntax for doing it...
You're usually better off dumping and reimporting, but I
believe the query below should do it if you really want to
try (make a safety dump first though)...

update pg_attribute set attnotnull='f' where
exists (select * from pg_class where pg_class.oid=attrelid
and pg_class.relname!~'^pg_') and attnotnull='t';

Stephan Szabo
sszabo@bigpanda.com

On Mon, 11 Sep 2000, Dan Browning wrote:

As far as I have been able to find (and I *could* be wrong) the only way to
remove a not null constraint (without drop/create) is to directly update the
pg_attribute.attnotnull field for the row. It is a boolean field so
updating it to 'false' does the trick. I hope this helps (and is the
correct way to do it, since this is what I have been doing!).

Darrin

From: "Dan Browning" <danb@cyclonecomputers.com>
To: <pgsql-general@postgresql.org>, <pgsql-novice@postgresql.org>
Subject: [GENERAL] Removing all NOT NULL constraints from multiple tables
easily
Date: Mon, 11 Sep 2000 06:32:27 -0700

I would like to remove all NOT NULL constraints from my tables.

I've tried ALTER TABLE, but I can't seem to get the syntax right, and the
docs aren't clear to me.
What is the correct syntax? If there's another way, short of manually
recreating (dump, create, import) the tables, I'd love to hear it.

Thanks,

Dan Browning
Network & Database Administrator
Cyclone Computer Systems

_________________________________________________________________________
Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.

Share information about yourself, create your own public profile at
http://profiles.msn.com.