What is wrong in a PostgreSQL query?

Question

PostgreSQL

there is a query like this where:
2 = car make
129 = mercedes
19 = steering wheel
2001569 = right side
20 = fuel
2001570 = diesel, 2001571 = gasoline
and it selects all Mercedes, all right hand drive, and all diesel cars and all cars on gasoline
, the problem is that it should select only Mercedes with a right-hand drive and with diesel and gasoline,
the query is greatly simplified
if you can explain what I am writing wrong

here is the table structure of
the SQL query:

SELECT *
FROM products p
WHERE p.id IN
     (SELECT DISTINCT pc.product_id
      FROM product_characteristic pc
      WHERE pc.product_id = p.id
        AND pc.characteristic_id IN   (2,     19,        20)
        AND pc.selected_option_id IN  (129,   2001569,   2001570, 2001571)
     )

Answer 1

SELECT *
FROM products p
WHERE EXISTS(SELECT 'x' FROM product_characteristic pc WHERE pc.characteristic_id = 2 AND pc.selected_option_id = 129 AND pc.product_id=p.id)
AND EXISTS(<второй фильтр>)
AND EXISTS(<третий фильтр>)

well, the product_characteristic index (characteristic_id, selected_option_id )

Answer 2

"In his personal file it was written - SPECIAL SIGNS - everything" (Dellamorte Dellamore)
1) It seems to me that you should seriously rework the structure of the database
2) Let's rephrase your request:

SELECT *
FROM products p
WHERE p.id IN
     (SELECT DISTINCT pc.product_id
      FROM product_characteristic pc
      WHERE pc.product_id = p.id
        AND pc.characteristic_id= ANY   (2,     19,        20)
        AND pc.selected_option_id=ANY (129,   2001569,   2001570, 2001571)
     )

Or, "completely in Russian"

SELECT *
FROM products p
WHERE p.id IN
     (SELECT DISTINCT pc.product_id
      FROM product_characteristic pc
      WHERE pc.product_id = p.id
        AND (pc.characteristic_id =2 OR  pc.characteristic_id =19 or pc.characteristic_id =20)
        AND (pc.selected_option=129 OR pc.selected_option=2001569 OR pc.selected_option=2001570 or pc.selected_option=2001571)
     )

That is, instead of crossing characteristics, you choose their union
. Well, since the subquery is generally independent and independent of the outer query, I don’t really understand what p.id does in it and how it will work in general.
3) If you try to write what you wanted, you get something like this

SELECT *
FROM products p
WHERE p.id IN
     (SELECT pc.product_id
      FROM product_characteristic pc,  product_characteristic pc1,product_characteristic pc2,  
      WHERE pc.product_id=pc1.product_id and pc.product_id=pc2.product_id
        AND pc.characteristic=2 AND pc.selected_option_id=129
        AND pc1.characteristic=19  AND pc1.selected_option_id=2001569
        AND pc2.characteristic=20  AND pc2.selected_option_id in  (2001570, 2001571)
     )

Firstly - we remove DISTINCT from the subquery - it will give us a list of product_id, and even if they are repeated, the main query will select only unique rows
Secondly - we add two more aliases to the product_characteristic table
for each alias we set our own condition on the id of the characteristic and the id of the selected options
Well, we inform you that the desired product_id for all product_characteristic tables is the same for you.
If your selected_option_id is unique for each option, you can exclude the selected_option_id field from the request,
then it will turn out something like this:

SELECT *
FROM products p
WHERE p.id IN
     (SELECT pc.product_id
      FROM product_characteristic pc,  product_characteristic pc1,product_characteristic pc2,  
      WHERE pc.product_id=pc1.product_id and  pc.product_id=pc2.product_id
        AND pc.selected_option_id=129
        AND pc1.selected_option_id=2001569
        AND pc2.selected_option_id in  (2001570, 2001571)
     )