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

Answer 2

You can do:

select * from t where not :myFlag or value in (1, 3)

See running example at db<>fiddle.

Answer 3

Yes, you can do pretty much exactly what you did, as long as you stick to the logic added in your later edit. Demo at db<>fiddle:

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

---

This:

if :myFlag is true, field in database must be in a list (values here), or null.

means this:

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

Meanwhile, these express a somewhat different logic:

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.

meaning this:

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

In the first paragraph, you do want the row {id:2,val:null} when :myFlag is true. The example you added later showed the opposite logic.

Funnily enough, @JGH's code you went with, expresses yet another variant:

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