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