I've got a collection of duplicate relations of relations that I need to tidy up.

The table might be described like this

id: 1

id: 1, post_id: 1, created_at: 2020-01-02
id: 2, post_id: 1, created_at: 2020-01-02
id: 3, post_id: 1, created_at: 2020-01-03

id: 1, impression_id: 1
id: 2, impression_id: 1
id: 3, impression_id: 2
id: 4, impression_id: 2
id: 5, impression_id: 3

I intend to remove the impression with id 1, but due to foreign key constraints, I would need to remove the related demographic_distributions first.

From demographic_distributions I would want to remove ids 1 & 2.

The following query works, but it's not very efficient. I have around 78 millions rows in the demographic_distribution table, and I have other similar sized related tables.

DELETE FROM demographic_distribution
WHERE impression_id NOT IN (
  FROM impressions AS i1
  WHERE id IN (
    SELECT MAX(id)
    FROM impressions AS i2
    WHERE i2.post_id = i1.post_id
      AND i2.created_at::date = i1.created_at::date
    GROUP BY i2.post_id    
  • What do you mean by "not very efficient"? How are you determining this? How many rows are you trying to delete? Out of how many? – Colin 't Hart Jan 29 at 16:08
  • And why the double nesting? This seems equivalent: DELETE FROM demographic_distribution WHERE impression_id NOT IN ( SELECT MAX(id) FROM impressions GROUP BY post_id, created_at::date) ; – ypercubeᵀᴹ Jan 29 at 16:22
  • There are duplicates in the impression table too. I'll be removing those in a separate query. The demographic_distribution table has about 78million rows. – Kevin Carmody Jan 29 at 16:22

You can do all in a single data modifying CTE

with deleted_impressions as (
  delete from impressions i1
  where exists (select * 
                from impressions i2
                where i2.id > i1.id
                  and i2.created_at = i1.created_at)
  returning id
), delete_reg_impressions as (
  delete from regional_impressions
  where impression_id in (select id 
                        from deleted_impressions)
delete from demographic_distribution
where impression_id in (select id 
                        from deleted_impressions);

First the duplicates from impressions are removed and all IDs that are returned from that query are then used to delete the corresponding rows in demographic_distribution as this is done in a single (atomic) statement the foreign keys are still valid.

|improve this answer|||||
  • Thanks for pointing me towards CTE, that looks really useful. Unfortunately in my case, I have a second, regional_distribution table of similar size that would also need those impression ids. – Kevin Carmody Jan 30 at 10:19
  • @KevinCarmody: you can add another delete to the statement without problems – a_horse_with_no_name Jan 30 at 10:20
  • appreciate the update. your edit introduced a syntax error – Kevin Carmody Jan 30 at 10:36

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