Finding pairs of keys with the same values postgresql

I want to write a SQL query that find the paires of keys that have the same values. Each key can have multible values.

Example of table:

id, key
a,  1
a,  2
b,  1
c,  1
c,  2

So in this example the only pair would be (a,c) because they both have the keys 1 and 2, while b only has the key 1.

To solve this problem I was thinking something like this

(SELECT id FROM table
WHERE table.id = '[id]')
EXCEPT
(SELECT id FROM table
WHERE table.id = '[id]')

Where [id] could be a, b or c. If that query returns no rows then they have the same values. But i’m stuck after that.

Answer

I am thinking two levels of aggregation:

select keys, array_agg(id) as ids
from (select id, array_agg(key order by key) as keys
      from t
      group by id
     ) t
group by keys
having count(*) > 1;

Advertisement

Answer