Re: Adding PRIMARY KEY: Table contains duplicated values

On 02/04/2013 06:17 AM, Alexander Farber wrote:
> 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)

SELECT * FROM (SELECT count(*) AS ct, id, author FROM pref_rep GROUP BY
id, author) AS dup WHERE dup.ct >1;

>
> Thank you
> Alex

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com