How to use CASE condition in WHERE in PostgreSQL?

Question

I have a Spring boot app with custom queries. I need to find all database rows according to condition - if :myFlag is true, field in database must be in a list (:values here), or null.
Can I use something like this:

select *
from test
where id<4
and (case
         when :myFlag then val in (:values)
         else val in (:values) or val is null
     end)

For example, if I have rows

id | val
1  | 1
2  | null
3  | 3

• When my values = [1,3], myFlag = true, then the query must return rows with id=1,3.
• If myFlag = false, then I need to retrieve rows with id=1,2,3.

Answer 1

You don't need a case here,

select ...
from ...
where ...
and (
  value in (:values)
  OR (:myFlag AND value IS NULL)
)