CODEWITHSUNDEEP
sqlpostgresqlboolean-logic
psql
psql

Reputation: 11

OR not working as expected in Postgres

In Postgresql when I make a SELECT using the OR does not seem to work. Maybe I'm doing something wrong? For example:

SELECT * 
FROM toorder 
WHERE product_id = 1193 
AND status = 'NO' 
OR status = 'YES'

The result give me results that are not product_id = 1193 also. The status field has 3 answers; YES, NO and DON in this example. Anybody has any idea why it does not work?

Upvotes: 1

Views: 492

Answers (2)

MatheusOl
MatheusOl

Reputation: 11825

The OR operator works just fine, what is happening is that what you are doing is in fact equivalent to:

-- WRONG (not expected):
SELECT * FROM toorder
WHERE (product_id = 1193 AND status = 'NO') OR (status = 'YES')

Which means you are getting all results with product_id = 1193 that have status = 'NO', but also all other (no matter which product_id) with status = 'YES'. That happens because AND operator has precedence over OR operator (in fact OR is the one with least precedence).

So, in your case you should use explicit parenthesis to make the precedence as you desire:

-- CORRECT (expected):
SELECT * FROM toorder
WHERE product_id = 1193 AND (status = 'NO' OR status = 'YES')

It is advisable to always use parenthesis when you mix different kind of operators, even in cases that you want the default precedence, it helps to make the code more clear and increase the maintainability.

Complementing... As @wildplasser suggested, in this specific case you can simplify things even more by using the IN operator:

SELECT * FROM toorder
WHERE product_id = 1193 AND status IN ('NO', 'YES')

Which is logically equivalent to the previous query.

Upvotes: 5

mwarren
mwarren

Reputation: 2469

SELECT * FROM toorder WHERE product_id = 1193 AND (status = 'NO' OR status = 'YES')

Put brackets round the logic that belongs together, otherwise the OR will run against all records, not just product_id = 1193

Upvotes: 1

Related Questions