ALTER TABLE ADD COLUMN can't use NOT NULL?

Bradley McLean <brad@bradm.net> writes:

In the CVS tip from this morning:
a123=# alter table test add test1 int4 not null;
ERROR: Adding NOT NULL columns is not implemented.
Add the column, then use ALTER TABLE ADD CONSTRAINT.

I'm pretty sure the first one used to work just fine.

No, it never worked per spec. The spec requires the constraint to
be enforced immediately, and since the values of the new column
would all be null, there's no way for this to be a legal command.

What's legal per spec is an ADD that provides a DEFAULT along with
specifying NOT NULL. But we don't support ADD with a DEFAULT yet :-(

regards, tom lane

Bradley McLean <brad@bradm.net> writes:

In the CVS tip from this morning:
a123=# alter table test add test1 int4 not null;
ERROR: Adding NOT NULL columns is not implemented.
Add the column, then use ALTER TABLE ADD CONSTRAINT.

I'm pretty sure the first one used to work just fine.

No, it never worked per spec. The spec requires the constraint to
be enforced immediately, and since the values of the new column
would all be null, there's no way for this to be a legal command.

What's legal per spec is an ADD that provides a DEFAULT along with
specifying NOT NULL. But we don't support ADD with a DEFAULT yet :-(

As far as I am aware, we don't even support using ALTER TABLE ADD CONSRAINT
to add a NOT NULL constraint, so I have no idea why the ERROR: message tells
people to do that!!!

Or am I wrong?

Chris

"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:

As far as I am aware, we don't even support using ALTER TABLE ADD CONSRAINT
to add a NOT NULL constraint, so I have no idea why the ERROR: message tells
people to do that!!!

Not directly, but you can add a CHECK(foo NOT NULL) constraint.

regards, tom lane

Thus spake Bradley McLean

a123=# alter table test add test1 int4 not null;
ERROR: Adding NOT NULL columns is not implemented.
Add the column, then use ALTER TABLE ADD CONSTRAINT.
a123=# alter table test add test1 int4 null;
ALTER
a123=#

I'm pretty sure the first one used to work just fine. Is this
intentional breakage?

Are you sure? I seem to recall that it was accepted but the constraint
was simply ignored. I thought that the recent change was just that it
rejected the attempt.

-- 
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.