Postgres Pro
Downloads
Home   >   mailing lists

Thread: 7.3.3 ADD COLUMN wierdness

7.3.3 ADD COLUMN wierdness

From
"Brian O'Donoghue"
Date:
I'm running two different strains of PostgreSQL

7.1.3, which is what our website is running off of now and 7.3.3, which we
hope to move to (sometime).

In 7.1.3 I can say

ALTER TABLE blah ADD COLUMN whatever integer not null;

and as expected, that works fine

on 7.3.3

the same command returns
Adding NOT NULL columns is not implemented.
        Add the column, then use ALTER TABLE ... SET NOT NULL.

Hmm, the last time I checked, this should have worked.
Is this a bug or a feature?

If it's a feature, can I turn it off, because this is a hassel.


________________________________________________________________________
This email has been scanned for all viruses by the MessageLabs Email
Security System. For more information on a proactive email security
service working around the clock, around the globe, visit
http://www.messagelabs.com
________________________________________________________________________

Re: 7.3.3 ADD COLUMN wierdness

From
Rod Taylor
Date:
On Mon, 2003-06-16 at 07:53, Brian O'Donoghue wrote:
> I'm running two different strains of PostgreSQL
>=20
> 7.1.3, which is what our website is running off of now and 7.3.3, which we
> hope to move to (sometime).
>=20
> In 7.1.3 I can say
>=20
> ALTER TABLE blah ADD COLUMN whatever integer not null;

CREATE TABLE bob(col integer);
INSERT INTO bob VALUES (1);
ALTER TABLE bob ADD COLUMN col2 integer NOT NULL;

Now of course, the table has a NULL value for col2 which is not allowed
per the NOT NULL constraint.

Until we allow processing of a DEFAULT expression during column
creation, this will not work that way.

You could argue we should allow NOT NULL to be added to an empty table.
--=20
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc

Re: 7.3.3 ADD COLUMN wierdness

From
"Brian O'Donoghue"
Date:
Ah yes.


ALTER TABLE blah ADD COLUMN whatever integer;
UPDATE blah set whatever=1;
ALTER TABLE blah ALTER COLUMN whatever SET NOT NULL;

Produces the desired effect.

I was thinking something along the lines of

ALTER TABLE blah ADD COLUMN whatever integer not null default 1;
should work, but the above is just as handy.

________________________________________________________________________
This email has been scanned for all viruses by the MessageLabs Email
Security System. For more information on a proactive email security
service working around the clock, around the globe, visit
http://www.messagelabs.com
________________________________________________________________________

Re: 7.3.3 ADD COLUMN wierdness

From
Tom Lane
Date:
"Brian O'Donoghue" <bodonoghue@stockbyte.com> writes:
> ALTER TABLE blah ADD COLUMN whatever integer not null;
> Adding NOT NULL columns is not implemented.
>         Add the column, then use ALTER TABLE ... SET NOT NULL.

> Hmm, the last time I checked, this should have worked.

Although 7.1 allowed that, it did not work correctly --- the NOT NULL
wasn't checked.  7.3 is aware that it can't handle it :-).

You can do ALTER TABLE ADD COLUMN, then UPDATE to fill all the rows
with a suitable value, then ALTER again to set the NOT NULL constraint.

Yeah, it's a pain, but no one's gotten around to writing the code that
would be needed to handle doing this in one step.

            regards, tom lane

Re: 7.3.3 ADD COLUMN wierdness

From
Tom Lane
Date:
"Brian O'Donoghue" <bodonoghue@stockbyte.com> writes:
> I was thinking something along the lines of
> ALTER TABLE blah ADD COLUMN whatever integer not null default 1;
> should work,

It should, we just haven't got round to implementing that combination
yet.

            regards, tom lane