Make IN clause behave like AND clause
IN is like writing = ANY, e.g.

regress=&gt; SELECT 1 = ANY (ARRAY[1, 2, 3]);
 ?column? 
----------
 t
(1 row)


A simplistic interpretation of your question would be to say that you're asking for = ALL, but that rarely makes sense:

regress=&gt; SELECT 1 = ALL (ARRAY[1, 2, 3]);
 ?column? 
----------
 f
(1 row)


What you really want is to find all posts that have a corresponding post_categories entry for each of a listed set of category_id.

As is usual in relational databases, as soon as you clearly frame the question, the solution starts writing its self.

You could do a multiple join, like Daniel suggests, but that's a pain because it requires dynamic SQL. Or you could use relational set operations.

Instead, I'd possibly use PostgreSQL's array features. This would be easier if you'd provided sample data, but I think you want something like the following, which finds each category that matches the post then does a check to see if all the categories match (the HAVING clause):

SELECT
   posts.id
FROM posts 
INNER JOIN post_categories ON posts.id = post_categories.post_id 
WHERE post_categories.category_id IN (1,2,3)
GROUP BY posts.id
HAVING array_agg(post_categories.category_id) @&gt; ARRAY[1,2,3]
LIMIT 10


Untested, since you didn't provide sample data in CREATE TABLE and INSERTs form, but that's the general idea. @&gt; means "array-contains"; I suspect it'll be more efficient than using array_agg(category_id ORDER BY category_id) and an equality test against a sorted ARRAY literal, because you avoid the sort on aggregation.

You could leave out the WHERE clause and this would still work, but it might be quite slow, as it'd fail to filter out posts in which none of the categories matched before doing aggregation.

BTW, LIMIT without ORDER BY is usually a bug. The database can return any set of 10 matching results it feels like, in any order.