add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
create table cars (id int);
create table car_tags (car_id int, tag_id int);
CREATE TABLE

CREATE TABLE
insert into cars
values (297360),(564800),(564830),(1720948),(123456),(234567),(345678);
INSERT 0 7
insert into car_tags
values (297360,5),(564800,3),(564800,4),(564830,2),(345678,4),
(1720948,1),(123456,3),(123456,4),(123456,6),(234567,3);
INSERT 0 10
SELECT
c.id,
ARRAY_AGG(DISTINCT t.tag_id ORDER BY t.tag_id) AS tag_ids
FROM
cars c INNER JOIN car_tags t
ON c.id = t.car_id
GROUP BY c.id
ORDER BY c.id
id tag_ids
123456 {3,4,6}
234567 {3}
297360 {5}
345678 {4}
564800 {3,4}
564830 {2}
1720948 {1} 
SELECT 7
SELECT
c.id,
ARRAY_AGG(DISTINCT t.tag_id ORDER BY t.tag_id) AS tag_ids
FROM
cars c INNER JOIN car_tags t
ON c.id = t.car_id
GROUP BY c.id
HAVING
COUNT(CASE WHEN t.tag_id = 3 THEN 1 END) > 0
AND COUNT(CASE WHEN t.tag_id = 4 THEN 1 END) > 0
ORDER BY c.id
id tag_ids
123456 {3,4,6}
564800 {3,4} 
SELECT 2
SELECT
c.id,
ARRAY_AGG(DISTINCT t.tag_id ORDER BY t.tag_id) AS tag_ids
FROM
cars c INNER JOIN car_tags t
ON c.id = t.car_id
WHERE t.tag_id IN (3,4)
GROUP BY c.id
HAVING
COUNT(CASE WHEN t.tag_id = 3 THEN 1 END) > 0
AND COUNT(CASE WHEN t.tag_id = 4 THEN 1 END) > 0
ORDER BY c.id
id tag_ids
123456 {3,4}
564800 {3,4} 
SELECT 2
SELECT
c.id,
ARRAY_AGG(DISTINCT t.tag_id ORDER BY t.tag_id) AS tag_ids
FROM
cars c INNER JOIN car_tags t
ON c.id = t.car_id
WHERE t.tag_id IN (3,4)
GROUP BY c.id
ORDER BY c.id
id tag_ids
123456 {3,4}
234567 {3}
345678 {4}
564800 {3,4} 
SELECT 4