Q:

Pick row that has multiple other rows in adjacent table

I have a table called conversations. Beside that, I have a table called conversation_participants.
When users create a conversation, they pick which other users they want to make it with. I do, tough, want to check before creating a new conversation, that it doesn't already exists with that batch of participants - if so I just want to reuse the existing one.
So my question is. How do I find a row in conversation that has the exact relations in conversation_participants.
My table looks like this (simplified).
   | conversations       |
   | ------------------- |
   | id      | int (pk)  |
   | created | timestamp |
   | ------------------- |

   | conversation_participants                |
   | ---------------------------------------- |
   | id           | int (pk)                  |
   | created      | timestamp                 |
   | conversation | int (fk to conversations) |
   | user         | int (fk to users)         |
   | read         | timestamp                 |
   | ---------------------------------------- |

Now the question is. How do I find the row in conversation that has the exact set of users in conversation_participants? It must be the exact set – not just a subset.

A:

You can use aggregation.  If you construct the list of participants in order in a string or array, you can use:
select cp.conversation
from conversation_participants cp
group by cp.conversation
having string_agg(user order by user, ',') = '1,2,3,4';

You can also do this without strings or arrays:
select cp.conversation
from conversation_participants cp
where user in (1, 2, 3, 4)
group by cp.conversation
having count(*) = 4;  -- all four users

If you pass the values in as an array:
select cp.conversation
from conversation_participants cp
where user i= any :ar
group by cp.conversation
having count(*) = cardinality(:ar)  -- all four users

