CASE...WHEN in WHERE clause in Postgresql
For those looking to use a CASE in the WHERE clause, in the above adding an else true
condition in the case block should allow the query to work as expected. In the OP, the case will resolve as NULL, which will result in the WHERE clause effectively selecting WHERE ... AND NULL, which will always fail.
SELECT *
FROM table
WHERE t1.id_status_notatka_1 = ANY (selected_type)
AND t1.id_status_notatka_2 = ANY (selected_place)
AND CASE
WHEN t2.id_bank = 12 THEN t1.id_status_notatka_4 = ANY (selected_effect)
ELSE true
END
The accepted answer works, but I'd like to share input for those who are looking for a different answer. Thanks to sagi, I've come up with the following query, but I'd like to give a test case as well.
Let us assume this is the structure of our table
tbl
id | type | status
-----------------------
1 | Student | t
2 | Employee | f
3 | Employee | t
4 | Student | f
and we want to select all Student rows, that have Status = 't', however, We also like to retrieve all Employee rows regardless of its Status.
if we perform SELECT * FROM tbl WHERE type = 'Student' AND status = 't'
we would only get the following result, we won't be able to fetch Employees
tbl
id | type | status
-----------------------
1 | Student | t
and performing SELECT * FROM tbl WHERE Status = 't'
we would only get the following result, we got an Employee Row on the result but there are Employee Rows that were not included on the result set, one could argue that performing IN
might work, however, it will give the same result set. SELECT * FROM tbl WHERE type IN('Student', 'Employee') AND status = 't'
tbl
id | type | status
-----------------------
1 | Student | t
3 | Employee | t
remember, we want to retrieve all Employee rows regardless of its Status, to do that we perform the query
SELECT * FROM tbl WHERE (type = 'Student' AND status = 't') OR (type = 'Employee')
result will be
table
id | type | status
-----------------------
1 | Student | t
2 | Employee | f
3 | Employee | t
No need for CASE EXPRESSION
, simply use OR
with parenthesis :
AND (t2.id_bank <> 12 OR t1.id_status_notatka_4 = ANY (selected_effect))