Re: Another unexpected behaviour

Thanks. But I am not looking for how to avoid the problem at this
moment,
I am more interested in why PostgreSQL is designed to work this way.

To make the problem more obvious,
drop table if exists t1;
create table t1 (f1 int);
create unique index uix_t1 on t1(f1) ;
insert into t1(f1) values (1), (2), (3);
select * from t1;
update t1 set f1 = f1 + 1
==> ERROR: duplicate key value violates unique constraint "uix_t1"
DETAIL: Key (f1)=(2) already exists.

delete from t1;
insert into t1(f1) values (3), (2), (1);
select * from t1;
==> Query returned successfully: 3 rows affected, 16 ms execution
time.
update t1 set f1 = f1 + 1

i.e. the same "update t1 set f1 = f1 + 1" on a table with 3 rows (1,
2, 3) will results in different results depend on how the order of the
data got inserted !?
That doesn't feel like very SQL (declarative, set-oriented langauge)

On Jul 19, 11:44 pm, chris(dot)trav(dot)(dot)(dot)(at)gmail(dot)com (Chris Travers) wrote:
> On Tue, Jul 19, 2011 at 10:41 PM,  <to(dot)(dot)(dot)(at)tuxteam(dot)de> wrote:
> > -----BEGIN PGP SIGNED MESSAGE-----
> > Hash: SHA1
>
> > On Mon, Jul 18, 2011 at 03:18:43PM -0700, Shianmiin wrote:
> >> setup:
> >> ====
> >> drop table if exists t1;
> >> create table t1 (f1 int);
> >> create unique index uix_t1 on t1(f1) ;
> >> insert into t1(f1) values (1), (2), (3);
> >> select * from t1;
>
> >> f1
>
> If you are trying to make this work, you can add an order by to the
> update and that allows you to avoid this problem.
>
>  Best Wishes,
> Chris Travers
>
> --
> Sent via pgsql-general mailing list (pgsql-gene(dot)(dot)(dot)(at)postgresql(dot)org)
> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general