Find Duplicate value in array based on specific column using PostgreSQL

Question

I have a table containing this three field , nodes, Memeber_id, id .there is two repetitive rows of id in entire table. Based on this id value in id column I want to find repetitive value in node array. for eg . -169116 id represents two nodes array {-167486,-49628} and {-43815,-49625,-49626,-49627,-49628} In both the array common value is -49628. so it should select -169116 id and -49628 value from array.

Julia Leder · Accepted Answer · 2020-02-11 17:09:26Z

2

Unnest the array and then simply group by count(node) > 1:

select node, id, count(*) 
from (select unnest(nodes) as node, member_id, id from node_test) as g
group by node, id having count(node) > 1;

This results in:

  node  |   id    | count
--------+---------+-------
 -49628 | -169116 |     2

answered Feb 11, 2020 at 17:09

Julia Leder

8265 silver badges8 bronze badges

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

1 Comment

poonam patel Over a year ago

This is exactly what I wanted @Julia Leder. Thank you

Collectives™ on Stack Overflow

Find Duplicate value in array based on specific column using PostgreSQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related