Alexander Farber <alexander.farber@xxxxxxxxx> wrote: > # alter table pref_rep add primary key(id, author); > NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index > "pref_rep_pkey" for table "pref_rep" > ERROR: could not create unique index "pref_rep_pkey" > DETAIL: Table contains duplicated values. > > How could I find those duplicated pairs of id and author? similar example: test=*# select * from foo; id1 | id2 -----+----- 1 | 1 1 | 2 1 | 3 2 | 1 2 | 2 2 | 3 1 | 2 3 | 1 3 | 2 3 | 3 3 | 1 (11 rows) Time: 0,151 ms test=*# alter table foo add primary key (id1,id2); NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "foo_pkey" for table "foo" ERROR: could not create unique index "foo_pkey" DETAIL: Key (id1, id2)=(1, 2) is duplicated. Time: 1,394 ms test=*# select id1, id2, count(*) as c from foo group by id1, id2 having count(*) > 1; id1 | id2 | c -----+-----+--- 3 | 1 | 2 1 | 2 | 2 (2 rows) Time: 0,331 ms HTH. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
