Postgres Pro
Downloads
Home   >   mailing lists

Thread: ALTER TABLE ADD COLUMN can't use NOT NULL?

ALTER TABLE ADD COLUMN can't use NOT NULL?

From
Bradley McLean
Date:
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.
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?

-Brad

Re: ALTER TABLE ADD COLUMN can't use NOT NULL?

From
Tom Lane
Date:
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

Re: ALTER TABLE ADD COLUMN can't use NOT NULL?

From
"Christopher Kings-Lynne"
Date:
> 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

Re: ALTER TABLE ADD COLUMN can't use NOT NULL?

From
Tom Lane
Date:
"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

Re: ALTER TABLE ADD COLUMN can't use NOT NULL?

From
darcy@druid.net (D'Arcy J.M. Cain)
Date:
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.