sqldatabasepostgresqlcasewhere-in

How to use CASE condition in WHERE in PostgreSQL?


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

Solution

  • You don't need a case here,

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