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))

Tags:

Sql

Postgresql