Question 365351 in PostgreSQL Question posted in
The official documentation can be found here.

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
  • 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.
Tags:

3

Answers


  1. 0

    You don’t need a case here,

    select ...
from ...
where ...
and (
  value in (:values)
  OR (:myFlag AND value IS NULL)
)
    Login or Signup to reply.
  2. 0

    You can do:

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

    See running example at db<>fiddle.

    Login or Signup to reply.
  3. 0

    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)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search