I want to be able to map an exact list of id's to a single column value they all share. I'm using Postgres. Here is an example:

 ------- --------- 
| team  | user_id |
 ------- --------- 
|   A   |    1    |
|   B   |    2    |
|   A   |    2    |
 ------- --------- 

The teams have a unique set of users attached to them (e.g., there can't be any two teams with the same exact users). I'd like to be able to find the team based on the set of users, so if I have a list of users [1, 2] the result should be A, but if I only have a list [1] the result should be B. I started out with using a list column (varchar[]) for the user ids, but realized I needed to normalize the data for other purposes. So what I've done is this:

Select an array aggregate of teams and users:

select c.team, array_agg(b.user_id) as users
                         from teams c group by c.team

with output

 ------- --------- 
| team  | users   |
 ------- --------- 
|   A   |  {1, 2} |
|   B   |    {2}  |
 ------- --------- 

Then I find the team based on an array between/comparison statement:

select a.team from teams a where a.team = (select b.team from (
    select c.team as team, array_agg(b.user_id) as users
                         from teams c group by c.team) b
    where b.users @> '{1, 2}' and b.users <@ '{1, 2}'
);

which results in

 ------- 
| team  |
 ------- 
|   A   |
 ------- 

Is there a more elegant/efficient way to do this other than using the array_agg function?

CodePudding user response：

You can achieve this with a single GROUP BY and HAVING clause:

select team
from teams
group by team
having array_agg(user_id order by user_id) = array[1,2];

Note the order by inside the array_agg. The order of the values must match the order of the values in the array on the right hand side of the =

If you can't guarantee that your "input list" is properly sorted, then you'll need to use the @> as you have done:

select team
from teams
group by team
having array_agg(user_id) @> array[1,2]
   and array_agg(user_id) <@ array[1,2]