Re: Select rows when all all ids of its children records matches

I tried :

WITH posts_tags_cte AS (

        SELECT post_id, array_agg(tag_id) as tags

        FROM posts_tags

        WHERE tag_id in (1, 2)

        GROUP BY post_id

)

SELECT posts.id FROM posts_tags_cte JOIN posts ON posts.id = posts_tags_cte.post_id

WHERE posts_tags_cte.tags @> array[1, 2]::int8[]

But it gives me all the posts.