CODEWITHSUNDEEP
sqlpostgresqlduplicates
Lennie
Lennie

Reputation: 2069

PostgreSQL - Fastest way to get duplicates

Need to quickly find duplicates in a table by a given field name.

I have the query below but it runs still for 1.5 minutes even after i did put index on the table field.

select * from (
  SELECT *,
  ROW_NUMBER() OVER(PARTITION BY b.name) AS rowCount
  FROM table b
) a
where a.rowCount > 1

Upvotes: 0

Views: 847

Answers (3)

tourist
tourist

Reputation: 4333

You can directly get the duplicate names without any extra columns apart from name in the select clause.

select name
from table
group by name
having count(*)>1

Upvotes: 0

NiVeR
NiVeR

Reputation: 9786

You can do it like this:

select name, count(*)
from table 
group by name
having count(*) > 1;

Upvotes: 1

user4843530
user4843530

Reputation:

Try this

select name, min(id), count(id)
from table
group by name
where count(id) > 1

and this will be a lot faster if you have the name column indexed.

Upvotes: 0

Related Questions