Split cases in WHERE clause based on boolean variable

Question

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 < / = / >.

Answer 1

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

Answer 2

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:

• Function Performance

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;