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

Gordon Linoff · Accepted Answer · 2021-02-12 22:51:57Z

5

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.

edited Feb 12, 2021 at 22:51

answered Feb 10, 2021 at 22:48

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Etienne Over a year ago

Thanks for helping me again today! There is a syntax error in the having part, and I'm not able to catch it. I made a fiddle to test it: dbfiddle.uk/…

Gordon Linoff Over a year ago

@Etienne . . . .Missing closing paren.

Etienne Over a year ago

Thank you, indeed a paren was missing, but there is still a syntax error. Do we need the where keyword in the filter syntax? By adding it, the query is working, but there is still too much results (items that have more colors than the queried list of colors is also output). Can you have a look to this dbfiddle please? dbfiddle.uk/… Thank again for your help!

Etienne Over a year ago

Thanks. The query is now running well, but it still return too much data on some conditions: if an item have more associated colors than the list of queried colors, it is also returned while it shouldn't. This is what is showing the link to the dbfiddle in my comment just above. I am still a beginner in SQL but will try to find a solution. However if you have any idea how to handle this case to make this query fully working, it would be much appreciated.

Gordon Linoff Over a year ago

@Etienne . . . That was due to the WHERE clause, which I inadvertently left in.

|

Collectives™ on Stack Overflow

PostgreSQL - Get rows that match exactly an array

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related