PostgreSQL how to find duplicates by aggregated value

Question

I have two tables. The screenshots with the following structure:

id (bigint)
originalFilename (string)
createdAt (Date)

And the second is screenshotSignatures:

screenshotId (foreign key for screenshots)
signature (bigint)

So the screenshots table has one to many relation. So now I need to find all duplicates. It means screenshot has exactly the same signatures. I tried to do following:

SELECT os.id, os."originalFilename", array_agg(signature) as signatures from "development".screenshots os
left join development."screenshotSignatures" s on os.id = s."screenshotId"
WHERE GROUP BY os.id, os."originalFilename" ;

and from this query I get the following result:

So now I need to find out the duplicates somehow, but I don't know to do it. I thought I can do it some how from screenshotSignatures table. As a result it will be nice to have smth like: screenshots (a,b,c) have the same set of signatures. Later I'll need to delete the oldest screenshots. Any ideas? Thank you

IVO GELOV · Accepted Answer · 2021-09-21 13:10:54Z

1

Simply use the HAVING clause:

WITH signature_list AS (
  SELECT os.id, os."originalFilename", array_agg(signature ORDER BY signature) as signatures 
  from "development".screenshots os
  left join development."screenshotSignatures" s on os.id = s."screenshotId"
  GROUP BY os.id, os."originalFilename"
)
SELECT signatures, array_agg(id) as duplicate_ids, array_agg(originalFilename) as duplicate_filenames
FROM signature_list 
GROUP BY signatures
HAVING COUNT(*) > 1

edited Sep 21, 2021 at 13:10

answered Sep 21, 2021 at 12:52

IVO GELOV

14.3k1 gold badge21 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Kirill Varikov Over a year ago

Thanks for your answer but it says: Unable to resolve column signatures in HAVING clause. Am I did smth wrong?

IVO GELOV Over a year ago

Okay, let's first aggregate the signatures for each filename - and then find duplicate signatures.

Kirill Varikov Over a year ago

It sounds cool, but how to do it? Could you provide some code?

IVO GELOV Over a year ago

I updated my answer - it does the aggregation inside the CTE and then uses the HAVING clause.

Kirill Varikov Over a year ago

Thank you man! You're my hero!

Collectives™ on Stack Overflow

PostgreSQL how to find duplicates by aggregated value

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related