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

Thank you -

On Mon, Feb 4, 2013 at 5:06 PM, Andreas Kretschmer
<akretschmer@spamfence.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