Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE colors(id serial primary key, name text unique);
create table items(id serial primary key, name text unique);

create table items_colors(item_id int, color_id int);

CREATE TABLE



CREATE TABLE



CREATE TABLE


INSERT INTO items(name)
VALUES('a'),('b'),('c')
RETURNING *;

INSERT INTO colors(name)
values('green'),('blue'),('red')
RETURNING *;

INSERT INTO items_colors(item_id, color_id)
VALUES(1,2),(1,3),(2,1),(3,1),(3,2),(3,3);


id
name


1
a

2
b

3
c



INSERT 0 3




id
name


1
green

2
blue

3
red



INSERT 0 3



INSERT 0 6


-- List all items with their colors
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
GROUP BY
    i.id


id
name
array_agg


1
a
{blue,red}

2
b
{green}

3
c
{green,blue,red}



SELECT 3


-- I should only get 'a' (but 'c' is also output).
-- Item 'c' has 3 colors (even if this query output only 2 of its colors),
-- and it should not be output since I need exactly `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


id
name
array_agg


1
a
{blue,red}

3
c
{blue,red}



SELECT 2