How to find out duplicate records in PostgreSQL?

@scotty_walker 

To find duplicate records in a PostgreSQL table, you can use the SELECT and GROUP BY clauses along with the HAVING clause. For example, consider the following table:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

CREATE TABLE users (
  id serial PRIMARY KEY,
  name varchar NOT NULL,
  email varchar NOT NULL
);

INSERT INTO users (name, email)
VALUES
  ('Alice', '[email protected]'),
  ('Bob', '[email protected]'),
  ('Charlie', '[email protected]'),
  ('Alice', '[email protected]'),
  ('Eve', '[email protected]'),
  ('Bob', '[email protected]');

To find duplicate records in the users table based on the name column, you can use the following query:

1
2
3
4

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

This will return all name values that appear more than once in the table, along with a count of how many times each value appears.

You can also use the DISTINCT keyword to return only the unique duplicate records:

1
2
3
4

SELECT DISTINCT name
FROM users
GROUP BY name
HAVING COUNT(*) > 1;

This will return all name values that appear more than once in the table, but will only return each value once.

You can modify this query to check for duplicates in other columns or combinations of columns by changing the GROUP BY and SELECT clauses accordingly. For example, to find duplicate records based on the combination of name and email, you can use the following query:

1
2
3
4

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

@scotty_walker 

This query will return all combinations of name and email that appear more than once in the table, along with a count of how many times each combination appears.