I have a database that contains some erroneous results that are essentially but not technically duplicates. The structure is like this:

id_page (PK), id_site, label, create_date

There should only be one entry for each site for each label but it turns out that I have some duplicates for site and label, where id_page and create_date are different. I would like to remove the rows with the lowest create_date.

I think I have come up with a solution for this but I'd appreciate feedback.

WITH duplicates as (
   SELECT id_page, id_site, count(id_site) over (partition by id_site) as ct, 
    min("create_date") over (partition by id_site) as dt
   from pages
   where label = '2018-12-15'
DELETE from pages
where id_page in (
   select p.id_page
   from duplicates as d
   join pages as p on (p.id_page = d.id_page
                   and p.create_date = d.dt)
WHERE ct = 2
  • You say where id_page and create_date are different. Does that mean both have to differ or (at least) one of both? Which columns can be NULL? Where do you consider NULL values equal? A table definition with all constraints (CREATE TABLE ...) would clarify. And always your version of Postgres. Jan 11, 2019 at 15:15
  • I would like to remove the rows with the lowest create_date. If there can be more than one dupe, then I assume you mean: I would like to keep one row with the highest create_date per set of peers. Can there be multiple peers with the same create_date? If so, how to break ties? Jan 11, 2019 at 15:24
  • No columns can be NULL. id_page is the PK so is always unique, create_date should be different. And, yes, your assumption is correct. If have (id_site = 50, create_date=1234) and (id_site=50 and create_date=2345), I'd like to keep the second row. Jan 11, 2019 at 16:36

2 Answers 2

WITH duplicates as 
    SELECT id_page, id_site,
           row_number() over (partition by id_site order by id_site, create_date) rn
    FROM   pages
    WHERE  label = '2018-12-15'
WHERE  id_page IN (SELECT id_page 
                   FROM   duplicates
                   WHERE  rn > 1);
  • Thanks, I'd seen something like this but I didn't know I could use row_number(). Only difference with my query is that this appears to return the id_page with the higher create_date Jan 11, 2019 at 18:26

For the information given, assuming all columns NOT NULL:

WHERE  label = '2018-12-15'
   WHERE  label       = p.label
   AND    id_site     = p.id_site
   AND    create_date > p.create_date

The logic, in plain English:

Delete rows with a given label where a row with the same label and id_site but later create_date exists.

This keeps the row with the latest date per set of peers (deleting 0-n duplicates).

If there can be duplicates with identical create_date:

WHERE  label = '2018-12-15'
   WHERE  label       =  p.label
   AND    id_site     =  p.id_site
   AND    (create_date, id_page) > (p.create_date, p.id_page) --!

... from ties on create_date keep the row with the greater id_page.

Note the row value comparison! See, with explanation:

Since id_page is the PK (as revealed in a comment), this is unambiguous. If in doubt, the second query is the safe bet, only slightly slower.

To make this fast, an index on (label, id_site) would be perfect - with index expressions in this order. (Might not pay to create it for a one-time operation.)

Fast without index

   SELECT id_page
        , row_number() OVER (PARTITION BY id_site
                             ORDER BY create_date DESC, id_page DESC) AS rn
   FROM   pages
   WHERE  label = '2018-12-15'
   ) del
WHERE  p.id_page = del.id_page 
AND    del.rn > 1;

Similar to McNets query, but breaking possible ties on create_date like the 2nd query above.

  • I'd never thought of doing it like this but the query seems to run for much longer than using a windowing function. Is this related to creating the cross table first? Jan 11, 2019 at 18:33
  • @CharlieClark: This query needs an index to be fast (typically faster than alternatives). I added details above. Else, a window function (or similar approach - there are many possible variants) will be faster. This query is short and clear in any case, performance may be of low importance for a one-time operation. Jan 11, 2019 at 18:45
  • Thanks for the additional information and the example with USING. This is, hopefully, a once off task and I was pleasantly surprised by the speed of the windowing approach. Took me a while to work my way through it but I now understand the approach much better than before. Jan 12, 2019 at 10:54
  • @CharlieClark: Consider the update for the 2nd query with row value comparison. Jan 12, 2019 at 14:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.