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

Unfortunately  that fails -

On Mon, Feb 4, 2013 at 3:55 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote:
> On 02/04/2013 06:45 AM, Alexander Farber wrote:
>>
>> Trying to delete the older of the duplicated pairs:
>>
>
> How about:
>
> SELECT id, author, count(1), max(stamp) as maxx
>   FROM pref_rep
> GROUP BY id, author
> HAVING count(1) >1 and stamp < max(stamp);

>> On Mon, Feb 4, 2013 at 3:36 PM, Alexander Farber
>> <alexander.farber@gmail.com> wrote:
>>>>> http://stackoverflow.com/questions/14688523/adding-primary-key-table-contains-duplicated-values

# SELECT id, author, count(1), max(stamp) as maxx
pref->   FROM pref_rep
pref-> GROUP BY id, author
pref-> HAVING count(1) >1 and stamp < max(stamp);
ERROR:  column "pref_rep.stamp" must appear in the GROUP BY clause or
be used in an aggregate function
LINE 4: HAVING count(1) >1 and stamp < max(stamp);