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.trav...@xxxxxxxxx (Chris Travers) wrote: > On Tue, Jul 19, 2011 at 10:41 PM, <to...@xxxxxxxxxx> 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...@xxxxxxxxxxxxxx) > To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
