Re: [HACKERS] Re: ALTER TABLE DROP COLUMN

At 10:20 AM 2/28/00 -0500, Tom Lane wrote:
>Don Baccus <dhogaza(at)pacifier(dot)com> writes:
>>> "update t set id=id+1" is also a 2x space,
>
>> And PG doesn't do it correctly anyway...
>
>? News to me. What's your definition of "correctly"?

create table foo(i integer unique);

(insert values)

donb=# select * from foo;
i
---
1
2
3
(3 rows)

donb=# update foo set i=i+1;
ERROR: Cannot insert a duplicate key into unique index foo_pkey

Shouldn't fail ... the constraint should be applied after the
update, but the row-by-row update of the index causes it to fail.
At least I presume that this is an artifact of PG implementing the
unique constraint by creating a unique index.

Stephan Szabo pointed this out to me awhile ago when we were
discussing "alter table add constraint" (he was looking into
this when he worked on "alter table add foreign key").

Of course, sometimes PG gets it right. I deleted stuff in foo,
then did:

donb=# insert into foo values(3);
INSERT 26907 1
donb=# insert into foo values(2);
INSERT 26908 1
donb=# insert into foo values(1);
INSERT 26909 1
donb=# update foo set i=i+1;
UPDATE 3
donb=#

- Don Baccus, Portland OR <dhogaza(at)pacifier(dot)com>
Nature photos, on-line guides, Pacific Northwest
Rare Bird Alert Service and other goodies at
http://donb.photo.net.