Re: Adding PRIMARY KEY: Table contains duplicated values

Thank you -

On Mon, Feb 4, 2013 at 5:06 PM, Andreas Kretschmer
<akretschmer(at)spamfence(dot)net> 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 do you get this DETAIL, is it a setting for psql prompt?

I've got a nice answer for my question at Stackoverflow:

DELETE FROM pref_rep p USING (
SELECT id, author, max(stamp) stamp
FROM pref_rep
GROUP BY id, author
HAVING count(1) > 1) AS f
WHERE p.id=f.id AND p.author=f.author AND p.stamp<f.stamp;

and learnt about SQL Fiddle too -
http://sqlfiddle.com/#!11/59fbc/11

Regards
Alex