How to find duplicate column values in PostgreSQL

Here’s a recipe for retrieving duplicate values in a SQL query (run on Postgres).

Consider a table called genres:

 id |      name
----+----------------
  1 | Pop
  2 | Rock
  3 | Blues
  4 | Pop

An enthusiastic listener appears to have added ‘Pop’ twice, and in this case there was no UNIQUE constraint to prevent it.

To find duplicates in genres, use a query like the following:

SELECT name, COUNT(*) FROM genres GROUP BY name HAVING COUNT(*) > 1;

This returns:

 name | count
------+-------
 Pop  |     2

Note that an alias can’t be used for the count in this case, because the HAVING clause is evaluated first.

This strategy can be used anywhere to find duplicates, or more generally to retrieve counts beyond any value, or some other criterion.