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

• DantheMan: Yes that is so obvious now; worked like a charm. Thank you so much!

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;