Re: Adding PRIMARY KEY: Table contains duplicated values

Thank you -

On Mon, Feb 4, 2013 at 3:26 PM, Andrew Jaimes <andrewjaimes(at)hotmail(dot)com> wrote:
> SELECT id, author, count(1)
> FROM pref_rep
> GROUP BY id, author
> HAVING count(1) >1

>> From: alexander(dot)farber(at)gmail(dot)com
>> http://stackoverflow.com/questions/14688523/adding-primary-key-table-contains-duplicated-values

this has worked and has delivered me 190 records
(I still wonder how they could have happened,
because I only used a stored procedure
with UPDATE - if NOT FOUND - INSERT
Is it maybe pgbouncer's fault?):

id | author | count
------------------------+------------------------+-------
DE10598 | OK495480409724 | 2
DE12188 | MR17925810634439466500 | 3
DE13529 | OK471161192902 | 2
DE13963 | OK434087948702 | 2
DE14037 | DE7692 | 2
......
VK45132921 | DE3544 | 2
VK6152782 | OK261593357402 | 2
VK72883921 | OK506067284178 | 2
(190 rows)

And then I'm trying to construct a query which
would delete the older (the "stamp" column)
of such pairs - but this also doesn't work:

# SELECT id, author, count(1), stamp
FROM pref_rep
GROUP BY id, author, stamp
HAVING count(1) >1;
id | author | count | stamp
----------------+-----------------------+-------+----------------------------
OK14832267156 | OK419052078016 | 2 | 2012-04-11 12:54:02.980239
OK333460361587 | VK151946174 | 2 | 2012-07-04 07:08:22.172663
OK351109431016 | OK165881471481 | 2 | 2011-09-18 18:29:33.51362
OK367507493096 | OK342027384470 | 5 | 2012-02-10 20:58:11.488184
OK430882956135 | OK331014635822 | 2 | 2012-11-21 18:38:23.141298
OK446355841129 | OK353460633855 | 2 | 2012-06-15 21:31:56.791688
OK450700410618 | OK511055704249 | 2 | 2012-03-16 15:19:50.27776
OK458979640673 | OK165881471481 | 2 | 2011-08-18 22:31:17.540112
OK468333888972 | MR5100358507294433874 | 2 | 2012-12-05 14:16:15.870061
OK485109177380 | DE12383 | 2 | 2011-09-16 16:00:38.625038
OK505164304516 | OK165881471481 | 2 | 2012-03-24 13:54:27.968482
(11 rows)

Any suggestions please? Should I use a temp table here?

Thank you
Alex