Re: Adding PRIMARY KEY: Table contains duplicated values

Alexander Farber  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°