Postgres Pro
Downloads
Home   >   mailing lists

Thread: Changing NOT NULL condition on column

Changing NOT NULL condition on column

From
Bob Smith
Date:
I need to change the not-null restriction on some columns in a table,
and this doesn't seem to be possible with ALTER TABLE.  Is it safe to
connect as superuser and update pg_attribute.attnotnull directly?  Or
is there a different way to do this, other than dropping/re-creating
the whole table?

I'm currently running 7.2.3, but will be upgrading soon so I'd like to
know if this would work with 7.3 as well.

Thanks!

Bob Smith
Hammett & Edison, Inc.
bsmith@h-e.com

Re: Changing NOT NULL condition on column

From
Tom Lane
Date:
Bob Smith <bsmith@h-e.com> writes:
> I need to change the not-null restriction on some columns in a table,
> and this doesn't seem to be possible with ALTER TABLE.  Is it safe to
> connect as superuser and update pg_attribute.attnotnull directly?

Yeah.  I am not certain offhand that currently-running backends would
notice such a change, but at worst starting new sessions would cause
them to honor it.

> I'm currently running 7.2.3, but will be upgrading soon so I'd like to
> know if this would work with 7.3 as well.

In 7.3 there are ALTER TABLE variants to handle this more cleanly.

            regards, tom lane

Re: Changing NOT NULL condition on column

From
"eladio rodriguez"
Date:
saludos.

en 7.3:
ALTER TABLE [ ONLY ] table [ * ]
    ALTER [ COLUMN ] column { SET | DROP } NOT NULL

en 7.2 no funciona.
tendrias que drop la columna y recrearla.


>From: Bob Smith <bsmith@h-e.com>
>To: pgsql-admin <pgsql-admin@postgresql.org>
>Subject: [ADMIN] Changing NOT NULL condition on column
>Date: Tue, 20 May 2003 16:40:34 -0700
>
>
>I need to change the not-null restriction on some columns in a table, and
>this doesn't seem to be possible with ALTER TABLE.  Is it safe to connect
>as superuser and update pg_attribute.attnotnull directly?  Or is there a
>different way to do this, other than dropping/re-creating the whole table?
>
>I'm currently running 7.2.3, but will be upgrading soon so I'd like to know
>if this would work with 7.3 as well.
>
>Thanks!
>
>Bob Smith
>Hammett & Edison, Inc.
>bsmith@h-e.com
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster

_________________________________________________________________
Charla con tus amigos en línea mediante MSN Messenger:
http://messenger.yupimsn.com/