title: PostgreSQL how to find duplicates by aggregated value
body: <p>I have two tables. The <em>screenshots</em> with the following structure:</p>
  <ol>
  <li>id (bigint)</li>
  <li>originalFilename (string)</li>
  <li>createdAt (Date)</li>
  </ol>
  <p>And the second is <em>screenshotSignatures</em>:</p>
  <ol>
  <li>screenshotId (foreign key for screenshots)</li>
  <li>signature (bigint)</li>
  </ol>
  <p>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:</p>
  <pre><code>SELECT os.id, os.&quot;originalFilename&quot;, array_agg(signature) as signatures from &quot;development&quot;.screenshots os
  left join development.&quot;screenshotSignatures&quot; s on os.id = s.&quot;screenshotId&quot;
  WHERE GROUP BY os.id, os.&quot;originalFilename&quot; ;
  </code></pre>
  <p>and from this query I get the following result: <a href="https://i.stack.imgur.com/1ACgn.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/1ACgn.png" alt="enter image description here" /></a></p>
  <p>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 <em>screenshotSignatures</em> 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</p>