Re: Adding PRIMARY KEY: Table contains duplicated values

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

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°