Search Postgresql Archives

Re: Adding PRIMARY KEY: Table contains duplicated values

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Thank you -

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

>> From: alexander.farber@xxxxxxxxx
>> 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


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux