1

I have to split a WHERE clause based on a boolean variable:

If ShowZeroVariable is true, I only what to show rows value 0 in a certain integer column.
If false I want to show all that are greater than 0.

Something like this:

select IntField from Table 
where  
IntField 
case 
when ShowZeroVariable  = 'true' then = '0'
else <= '0'
end

I can only get it to kind of work by casting to text (But this doesn't filter out the zero values when false):

select IntField from Table 
where  
IntField::text like
case 
when ShowZeroVariable  = 'true' then '0'
else '%'
end

Can't seem to get a WHERE clause CASE statement to work with operators < / = / >.

2 Answers 2

2

CASE probably isn't the right tool here. But you can use a Boolean expression.

...
WHERE showzerovariable
      AND intfield = 0
       OR NOT showzerovariable
          AND intfield > 0
...
1
  • Yes that is so obvious now; worked like a charm. Thank you so much!
    – DantheMan
    Jun 10, 2019 at 17:35
0

CASE isn't too bad either:

...
WHERE CASE showzerovariable
         WHEN true  THEN int_column = 0
         WHEN false THEN int_column > 0
      END

Simpler if the variable is defined NOT NULL:

...
WHERE CASE WHEN showzerovariable THEN int_column = 0 ELSE int_column > 0 END

But if your query can benefit from indexes this is not working nicely with prepared statements (showzerovariable being the variable input). (If more than a few percent of all rows meet each condition, indexes are useless.) And PL/pgSQL likes to treat queries as prepared statements - if some preconditions are met. See:

If indexes are useful for at least one sub-case and you are indeed using PL/pgSQL, consider splitting cases to optimize performance. Like:

IF showzerovariable THEN
   RETURN QUERY
   SELECT ...
   WHERE  int_column = 0;
ELSE
   RETURN QUERY
   SELECT ...
   WHERE  int_column > 0;
END IF;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.