Ensuring a GROUP has specific values, no more no less with an aggregate I have a table (a :: text, b :: uuid).

I also have a set (not a list, i.e. order doesn’t matter) of UUIDs, bs = {b1, b2, b3}.

How can I get such an a that has exactly those three bs in the table, no more and no less?

Something like:

SELECT tt.a
  FROM (SELECT a, sort(array_agg(b)) AS bs FROM t GROUP BY a) AS tt
  WHERE tt.bs = sort('{b1, b2, b3}')


But that seems like quite a bad idea w.r.t. efficiency…
I think you want something like this..

SELECT a
FROM tt
GROUP BY a
HAVING array_agg(b ORDER BY b) = ARRAY[
  -- whatever
]::uuid[];


sample data,

CREATE TABLE tt ( a text, b uuid DEFAULT gen_random_uuid() );
INSERT INTO tt(a) VALUES ('foo'), ('bar'), ('baz'), ('foo'), ('foo');

SELECT a, b FROM tt ORDER BY a,b;
  a  |                  b                   
-----+--------------------------------------
 bar | 6a7d1d36-d16f-459c-9fb0-b5a54f1fc5b3
 baz | 2fca5dee-4bfe-4862-9b8a-79890fbfda28
 foo | 32f9d608-f87f-4830-b0d8-6215f60a71ee
 foo | 3fe0cffe-446e-4e94-8785-2f893ee91a0c
 foo | f9d07c5a-e69b-4501-a441-5bbd86ed7f4b
(5 rows)


This will return foo,

SELECT a
FROM tt
GROUP BY a
HAVING array_agg(b ORDER BY b) = ARRAY[
  '32f9d608-f87f-4830-b0d8-6215f60a71ee',
  '3fe0cffe-446e-4e94-8785-2f893ee91a0c',
  'f9d07c5a-e69b-4501-a441-5bbd86ed7f4b'
]::uuid[];


However, without exactly those three things and nothing more in the array_agg and in that order and you won't get a match.

If you don't care about excess matches, that is to say if foo having addition UUIds should return a match you can optimize this expression by copying them into an additional where clause which will use an index.

Shy of that, if you need faster you could move to use Materialized Views, or write a count clause too...

HAVING count(*) = ...
  AND array_agg(b ORDER BY b) = ...