Re: Adding PRIMARY KEY: Table contains duplicated values

Andreas Kretschmer <akretschmer(at)spamfence(dot)net> wrote:

> Alexander Farber <alexander(dot)farber(at)gmail(dot)com> 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°