Given three tables:

A users table with id's:

║ id ║
║ 1  ║
║ 2  ║
║ 3  ║

A purposes table with user_id's and name's:

║ id │ user_id │ name     ║
║ 1  │ 3       │ Business ║
║ 2  │ 2       │ Personal ║
║ 3  │ 2       │ Personal ║

And a journeys table with user_id's and purpose_id's.

║ id │ user_id │ purpose_id ║
║ 1  │ 2       │ 3          ║
║ 2  │ 1       │ 2          ║
║ 3  │ 1       │ 1          ║

We want to construct a query, in PostgreSQL, where we find all of the records in the purposes table that have the same name and user_id, deleting those with the least number of associated journeys while preserving the one record with the maxmium number of journeys while also updating the purpose_id of those journeys whose associated purpose has been deleted.

Right now I have a query to find those duplicate purposes using a window function, however the result only returns one of the duplicates and it's not certain they it has the maximum number of journeys (as I haven't specified that in any of the clauses).

select id from (
  over(partition by user_id, name) as dupe_count
) purposes
where purposes.dupe_count > 1;
  • Please always declare the version of Postgres you are working with. SELECT version() helps. It would help a lot to provide actual table definitions (CREATE TABLE statements showing data types and constraints). And what shall be the tiebreaker if multiple purposes have an equal number of journeys? – Erwin Brandstetter Oct 24 '18 at 20:54
  • Gotcha. Will post results of select version() from here on out along with actual table definitions. Thanks again. – dynsne Oct 25 '18 at 22:22

Can be a single statement with data-modifying CTEs:

WITH blacklist AS (
   FROM  (
      SELECT id
           , first_value(id) OVER (PARTITION BY user_id, name
                                   ORDER BY j_ct DESC NULLS LAST, id) AS master_id
                                                               -- id as tiebreaker
      FROM   purposes
      LEFT   JOIN (  -- left join to preserve rows without any journeys
         SELECT purpose_id AS id, count(*) AS j_ct
         FROM   journeys
         GROUP  BY 1
         ) j USING (id)
      ) sub
   WHERE  master_id <> id  -- is not master_id
 , del AS (
   DELETE FROM purposes p
   USING  blacklist b
   WHERE  b.id = p.id
UPDATE journeys j
SET    purpose_id = b.master_id
FROM   blacklist b
WHERE  b.id = j.purpose_id;

This should do exactly what you need - even in cases of ties or where no journeys exist at all.

Step by step

The 1st CTE blacklist identifies all IDs in table purposes where another row exists with the same (user_id, name) but more associated journeys. Remember that other ID as master_id to reassign journeys further down the road. id serves as tiebreaker: the smallest id prevails.

The 2nd CTE del deletes the identified dupes.
The outer UPDATE reroutes journeys to their new master.

Note DESC NULLS LAST in the ORDER BY clause. Important for cases with no journeys at all, since that result in a NULL value for the journey count j_ct. See:

Use the window function first_value() to get the master_id for each group of peers - after forming groups with PARTITION BY and sorting properly with ORDER BY.

  • Wow, thanks Erwin. I am learning a lot from your solution! Though, I should note, that looking at the docs for the first_value() window function shows that it takes an argument (the first value of what?) so for my problem I simply used first_value(id). – dynsne Oct 25 '18 at 22:21
  • @dynsne: Right, id was missing. – Erwin Brandstetter Oct 26 '18 at 2:07

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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