PostgreSQL - Get rows that match exactly an array

Question

I've a colors table and an items table, with a many to many relation between these 2 tables (via an items_colors table). An item can have many colors and a color can have many items. And there is no duplicated colors.

items
   id

colors
   id
   name

items_colors
    item_id [foreign key: items(id)]
    color_id [foreign key: colors(id)]

• How I can get all items that have exactly a specified array of colors? (no more no less)

---

For exemple I want to get all items that have exactly a blue and a red color (if it have another color or just one of the 2 colors, it must be ignored).

I'm still not able to get it... For exemple the query below is returning too much items (for exemple an item that have another color in addition to blue and red).

SELECT
    i.*,
    array_agg(c.name)
FROM
    items i
    JOIN items_colors ic ON ic.item_id = i.id
    JOIN colors c ON c.id = ic.color_id
WHERE
    c.name IN ('blue', 'red')
GROUP BY
    i.id
HAVING count(DISTINCT c.name) = 2

Here is a fiddle to play with my issue: https://dbfiddle.uk/?rdbms=postgres_13&fiddle=9de03c642da5b018d6f20b44bcf91876

Answer 1

You can use group by and having:

SELECT i.*
FROM items i JOIN
     items_colors ic
     ON ic.item_id = i.id JOIN
     colors c
     ON c.id = ic.color_id
GROUP BY i.id
HAVING COUNT(*) FILTER (WHERE c.name = ANY (ARRAY['blue', 'red'] ) ) = COUNT(*) AND
       COUNT(*) = CARDINALITY(ARRAY['blue', 'red']);

This assumes that neither the data nor the array have duplicates. It can be tweaked to handle that, but it seems unlikely for this type of problem.