Postgres Pro
Downloads
Home   >   mailing lists

Thread: Removing all NOT NULL constraints from multiple tables easily

Removing all NOT NULL constraints from multiple tables easily

From
"Dan Browning"
Date:
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

Re: Removing all NOT NULL constraints from multiple tables easily

From
Stephan Szabo
Date:
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:

> 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
>

Re: Removing all NOT NULL constraints from multiple tables easily

From
"Darrin Ladd"
Date:
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.