Find duplicates comparing 2 fields in PostgreSQL

Question

I've a table with the following data

id  parent_id   ascii_name  lang
1   123         Foo         en
2   123         Foo         fi
3   456         Bar         it
4   345         Foo         fr

I want to select all the records that have the same parent_id and ascii_name, basically I want this:

id  parent_id   ascii_name  lang
1   123         Foo         en
2   123         Foo         fi

Right now I was able to select the records having only the same ascii_name:

id  parent_id   ascii_name  lang
1   123         Foo         en
2   123         Foo         fi
4   345         Foo         fr

using the query:

SELECT * FROM table WHERE ascii_name in 
(SELECT ascii_name FROM table GROUP By ascii_name
 HAVING "count"(ascii_name) > 1)

I don't know how to put the parent_id into the equation.

Update

I found the right query using both @jakub and @mucio answers:

SELECT * FROM geo_nodes_copy WHERE (parent_id,ascii_name) in 
(SELECT parent_id, ascii_name 
 FROM geo_nodes_copy 
 GROUP By parent_id, ascii_name 
 HAVING count (1) > 1)

Now, the only problem is, maybe, the query speed.

Much like this: stackoverflow.com/questions/54418/…

Erwin Brandstetter
– Erwin Brandstetter

2015-04-16 13:33:25 +00:00
Commented Apr 16, 2015 at 13:33 — Erwin Brandstetter
– Erwin Brandstetter, Commented Apr 16, 2015 at 13:33

mucio · Accepted Answer · 2015-04-16 10:13:39Z

1

Use the following query as subquery

   SELECT parent_id, 
          ascii_name 
     FROM table 
 GROUP By parent_id, 
          ascii_name 
   HAVING count (1) > 1

This will return you all the couple parent_id/ascii_name with multiple rows.

answered Apr 16, 2015 at 10:13

mucio

7,1271 gold badge23 silver badges36 bronze badges

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

Comments

Jakub Kania · Accepted Answer · 2015-04-16 13:00:48Z

1

Well, since it's pg you can use a row construct:

SELECT * FROM table WHERE (ascii_name,parent_id) in 
(SELECT ascii_name, parent_id FROM table GROUP By ascii_name, parent_id HAVING Count(ascii_name) > 1)

edited Apr 16, 2015 at 13:00

answered Apr 16, 2015 at 10:14

Jakub Kania

16.6k2 gold badges44 silver badges51 bronze badges

3 Comments

Vivek S. Over a year ago

this will not run because you need to add parent_id in the GROUP BY clause

Fed03 Over a year ago

@vivek yeah, that's what I used, the problem now is performance

Vivek S. Over a year ago

@Fed03 This answer can be use for better performance

Gordon Linoff · Accepted Answer · 2015-04-17 23:04:54Z

1

Use window functions:

select t.*
from (select t.*, count(*) over (partition by ascii_name, parent_id) as cnt
      from table t
     ) t
where cnt >= 2;

Under some circumstances, it might be a bit faster to use exists:

select t.*
from table t
where exists (select 1
              from table t2
              where t2.ascii_name = t.ascii_name and
                    t2.parent_id = t.parent_id and
                    t2.id <> t.id
             );

For performance, include an index on table(ascii_name, parent_id, id).

edited Apr 17, 2015 at 23:04

answered Apr 16, 2015 at 10:46

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

2 Comments

Fed03 Over a year ago

the first solution doesn't work, the second yes! For the index, do you mena a combined index of the 3 columns?

Gordon Linoff Over a year ago

@vivek . . . There was an off-by-one error in the first answer.

Matt · Accepted Answer · 2015-04-16 10:13:22Z

0

Assuming that a parentid will always share the same asciiname

SELECT a.* 
FROM table a
WHERE a.ascii_name =
(SELECT b.ascii_name 
 FROM table b
 WHERE a.parent_id = b.parent_id)

answered Apr 16, 2015 at 10:13

Matt

15.1k28 gold badges109 silver badges161 bronze badges

Collectives™ on Stack Overflow

Find duplicates comparing 2 fields in PostgreSQL

Update

4 Answers 4

Comments

3 Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Update

4 Answers 4

Comments

3 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related