In the following archived email:

http://www.postgresql.org/mhonarc/pgsql-admin/2000-05/msg00025.html

this was posed as a solutions to modifying the NOT NULL constraint:

>update pg_attributes set attnotnull = 'f' where oid = oidofnotnullcolumn;
>vacuum analyze;

I didn't find any further comment on this so I decided to go right to the
source...

Is this recommended or not?
Are there any side effects of which I should be aware before attempting to
use this?

If this is not a valid way to accomplish the modification of the NOT NULL
constraint, then are there plans for an implementation of it (I enjoy the
new ALTER COLUMN DEFAULT)?

Thanks,
-Dan Wilson
phpPgAdmin Author
http://www.phpwizard.net/phpPgAdmin

Please reply to me directly as I'm not subscribed to the list.


Tue, 13 Jun 2000 21:25:18 -0600
"Dan Wilson" <phpPgAdmin@acucore.com>
Modifying NOT NULL Constraint
"Dan Wilson" <phpPgAdmin@acucore.com> writes:
> this was posed as a solutions to modifying the NOT NULL constraint:
>> update pg_attributes set attnotnull = 'f' where oid = oidofnotnullcolumn;
>> vacuum analyze;

attnotnull is where the gold is hidden, all right.  The 'vacuum analyze'
step is mere mumbo-jumbo --- there's no need for that.

> Are there any side effects of which I should be aware before attempting to
> use this?

Changing in that direction should be safe enough.  Turning attnotnull
*on* is a little more dubious, since it won't magically make any
existing null entries in the column go away.  attnotnull just governs
the check that prevents you from storing new nulls.

			regards, tom lane

Wed, 14 Jun 2000 00:33:15 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Modifying NOT NULL Constraint 
So if I'm understanding this correctly, this would be able to remove the NOT
NULL constraint, but would not be able to set the NOT NULL constraint.  Is
that correct?

If that is correct, are their plans to implement a post-create setting of
the NOT NULL constraint?

-Dan

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Dan Wilson" <phpPgAdmin@acucore.com>
Cc: <pgsql-hackers@postgresql.org>
Sent: Tuesday, June 13, 2000 10:33 PM
Subject: Re: [HACKERS] Modifying NOT NULL Constraint


> "Dan Wilson" <phpPgAdmin@acucore.com> writes:
> > this was posed as a solutions to modifying the NOT NULL constraint:
> >> update pg_attributes set attnotnull = 'f' where oid =
oidofnotnullcolumn;
> >> vacuum analyze;
>
> attnotnull is where the gold is hidden, all right.  The 'vacuum analyze'
> step is mere mumbo-jumbo --- there's no need for that.
>
> > Are there any side effects of which I should be aware before attempting
to
> > use this?
>
> Changing in that direction should be safe enough.  Turning attnotnull
> *on* is a little more dubious, since it won't magically make any
> existing null entries in the column go away.  attnotnull just governs
> the check that prevents you from storing new nulls.
>
> regards, tom lane


Wed, 14 Jun 2000 00:17:32 -0600
"Dan Wilson" <dan_wilson@geocities.com>
Re: Modifying NOT NULL Constraint 
"Dan Wilson" <dan_wilson@geocities.com> writes:
> So if I'm understanding this correctly, this would be able to remove the NOT
> NULL constraint, but would not be able to set the NOT NULL constraint.  Is
> that correct?

Oh, you can set attnotnull if you feel like it.  My point is just that
nothing much will happen to any existing null values in the column.
It's up to you to check for them first, if you care.

> If that is correct, are their plans to implement a post-create setting of
> the NOT NULL constraint?

What do you think should happen if there are null values?  Refuse the
command?  Delete the non-compliant rows?  Allow the rows to remain
even though the column is now nominally NOT NULL?

You can implement any of these behaviors for yourself with a couple of
SQL commands inside a transaction, so I'm not sure that I see the need
to have a neatly-wrapped-up ALTER TABLE command that will only do one
of the things you might want it to do.

			regards, tom lane

Wed, 14 Jun 2000 02:31:07 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Modifying NOT NULL Constraint 
Ok... point taken!  I guess the masters always have reasons for why things
aren't implemented.

-Dan


----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Dan Wilson" <dan_wilson@geocities.com>
Cc: <pgsql-hackers@postgresql.org>
Sent: Wednesday, June 14, 2000 12:31 AM
Subject: Re: [HACKERS] Modifying NOT NULL Constraint


> "Dan Wilson" <dan_wilson@geocities.com> writes:
> > So if I'm understanding this correctly, this would be able to remove the
NOT
> > NULL constraint, but would not be able to set the NOT NULL constraint.
Is
> > that correct?
>
> Oh, you can set attnotnull if you feel like it.  My point is just that
> nothing much will happen to any existing null values in the column.
> It's up to you to check for them first, if you care.
>
> > If that is correct, are their plans to implement a post-create setting
of
> > the NOT NULL constraint?
>
> What do you think should happen if there are null values?  Refuse the
> command?  Delete the non-compliant rows?  Allow the rows to remain
> even though the column is now nominally NOT NULL?
>
> You can implement any of these behaviors for yourself with a couple of
> SQL commands inside a transaction, so I'm not sure that I see the need
> to have a neatly-wrapped-up ALTER TABLE command that will only do one
> of the things you might want it to do.
>
> regards, tom lane


Wed, 14 Jun 2000 00:33:29 -0600
"Dan Wilson" <phpPgAdmin@acucore.com>
Re: Modifying NOT NULL Constraint 
Tom Lane wrote:

> What do you think should happen if there are null values?  Refuse the
> command?  Delete the non-compliant rows?  Allow the rows to remain
> even though the column is now nominally NOT NULL?

I would vote for refuse the command. It enforces the integrity of the
data.
You can always do an appropriate update command first if you think there
are 
nulls in there.

Wed, 14 Jun 2000 16:35:45 +1000
Chris Bitmead <chrisb@nimrod.itg.telstra.com.au>
Re: Modifying NOT NULL Constraint
> What do you think should happen if there are null values?  Refuse the
> command?  Delete the non-compliant rows?  Allow the rows to remain
> even though the column is now nominally NOT NULL?

With ALTER TABLE ADD CONSTRAINT on a non-deferrable NOT
NULL it should fail.  At the end of statement the constraint is not
satified,
an exception is raised and the statement is effectively ignored. It's alot
more complicated for deferrable constraints, and I didn't even actually
take that into account when I did the foreign key one (because I just
thought
of it now).

> You can implement any of these behaviors for yourself with a couple of
> SQL commands inside a transaction, so I'm not sure that I see the need
> to have a neatly-wrapped-up ALTER TABLE command that will only do one
> of the things you might want it to do.
True, but it would be nice to be able to add a check constraint later, and
as
long as you're doing it, it seems silly to ignore NOT NULL.



Wed, 14 Jun 2000 12:25:41 -0700
"Stephan Szabo" <sszabo@kick.com>
Re: Modifying NOT NULL Constraint 
Stephan Szabo wrote:
> > What do you think should happen if there are null values?  Refuse the
> > command?  Delete the non-compliant rows?  Allow the rows to remain
> > even though the column is now nominally NOT NULL?
>
> With ALTER TABLE ADD CONSTRAINT on a non-deferrable NOT
> NULL it should fail.  At the end of statement the constraint is not
> satified,
> an exception is raised and the statement is effectively ignored. It's alot
> more complicated for deferrable constraints, and I didn't even actually
> take that into account when I did the foreign key one (because I just
> thought
> of it now).

    Forget it!

    Doing

        BEGIN;
        ALTER TABLE tab ADD CONSTRAINT ... INITIALLY DEFERRED;
        UPDATE tab SET ... WHERE ... ISNULL;
        COMMIT;

    is  totally pathetic. Do it the other way round and the ALTER
    TABLE is happy.  As Tom usually says "if it hurts,  don't  do
    it". We have more important problems to spend our time for.


Jan


BTW: Still have your other FK related mail to process. Will do so soon.

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



Wed, 14 Jun 2000 22:59:48 +0200 (MEST)
JanWieck@t-online.de (Jan Wieck)
Re: Modifying NOT NULL Constraint
Well, I wasn't planning on doing it any time soon... I just wanted to
mention it for
completeness-sake since it was my code that does it "wrong" and I'd rather
mention
it than have someone come back to me asking me why my code does what it
does.
The basic point is that ALTER TABLE isn't too much of a difference from
normal
constraint checking...  If the constraint fails when the ALTER TABLE is done
the
statement should abort just like any other statement that causes a
constraint failure.

> Stephan Szabo wrote:
> > > What do you think should happen if there are null values?  Refuse the
> > > command?  Delete the non-compliant rows?  Allow the rows to remain
> > > even though the column is now nominally NOT NULL?
> >
> > With ALTER TABLE ADD CONSTRAINT on a non-deferrable NOT
> > NULL it should fail.  At the end of statement the constraint is not
> > satified,
> > an exception is raised and the statement is effectively ignored. It's
alot
> > more complicated for deferrable constraints, and I didn't even actually
> > take that into account when I did the foreign key one (because I just
> > thought
> > of it now).
>
>     Forget it!
>
>     Doing
>
>         BEGIN;
>         ALTER TABLE tab ADD CONSTRAINT ... INITIALLY DEFERRED;
>         UPDATE tab SET ... WHERE ... ISNULL;
>         COMMIT;
>
>     is  totally pathetic. Do it the other way round and the ALTER
>     TABLE is happy.  As Tom usually says "if it hurts,  don't  do
>     it". We have more important problems to spend our time for.



Wed, 14 Jun 2000 16:05:34 -0700
"Stephan Szabo" <sszabo@kick.com>
Re: Modifying NOT NULL Constraint