Andreas Kretschmer <akretschmer@xxxxxxxxxxxxx> wrote: > 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 If your next question is 'how to delete ...', my answer: (yeah, reading SO ;-) ) test=*# select ctid,* from foo; ctid | id1 | id2 --------+-----+----- (0,1) | 1 | 1 (0,2) | 1 | 2 (0,3) | 1 | 3 (0,4) | 2 | 1 (0,5) | 2 | 2 (0,6) | 2 | 3 (0,7) | 1 | 2 (0,8) | 3 | 1 (0,9) | 3 | 2 (0,10) | 3 | 3 (0,11) | 3 | 1 (11 rows) Time: 0,170 ms test=*# delete from foo where ctid in (select min(ctid) from foo where (id1,id2) in (select id1, id2 from foo group by id1, id2 having count(*) > 1) group by id1,id2); DELETE 2 Time: 0,559 ms test=*# select ctid,* from foo; ctid | id1 | id2 --------+-----+----- (0,1) | 1 | 1 (0,3) | 1 | 3 (0,4) | 2 | 1 (0,5) | 2 | 2 (0,6) | 2 | 3 (0,7) | 1 | 2 (0,9) | 3 | 2 (0,10) | 3 | 3 (0,11) | 3 | 1 (9 rows) 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
