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.klaver@xxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
