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

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

"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

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

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.

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.

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 #

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