Adding PRIMARY KEY: Table contains duplicated values - Mailing list pgsql-general

Hello,

when trying to add a forgotten primary key pair
to a PostgreSQL 8.4.13 table I get the error:

# \d pref_rep
                                       Table "public.pref_rep"
  Column   |            Type             |                         Modifiers
-----------+-----------------------------+-----------------------------------------------------------
 id        | character varying(32)       |
 author    | character varying(32)       |
 good      | boolean                     |
 fair      | boolean                     |
 nice      | boolean                     |
 about     | character varying(256)      |
 stamp     | timestamp without time zone | default now()
 author_ip | inet                        |
 rep_id    | integer                     | not null default
nextval('pref_rep_rep_id_seq'::regclass)
Check constraints:
    "pref_rep_check" CHECK (id::text <> author::text)
Foreign-key constraints:
    "pref_rep_author_fkey" FOREIGN KEY (author) REFERENCES
pref_users(id) ON DELETE CASCADE
    "pref_rep_id_fkey" FOREIGN KEY (id) REFERENCES pref_users(id) ON
DELETE CASCADE

# 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?

I've tried following, but this of course doesn't give me "pairs":

# select id, count(id) from pref_rep group by id order by count desc limit 5;
       id       | count
----------------+-------
 OK408547485023 |   706
 OK261593357402 |   582
 DE11198        |   561
 DE13041        |   560
 OK347613386893 |   556
(5 rows)

Thank you
Alex

P.S. I've also asked my question also at SO,
         hope it is okay to "crosspost" that way :-)

http://stackoverflow.com/questions/14688523/adding-primary-key-table-contains-duplicated-values