CODEWITHSUNDEEP
sqlpostgresqlcase
Andrew Fox
Andrew Fox

Reputation: 133

Combine CASE and BETWEEN in WHERE clause

I'm trying to execute what I presumed to be a simple enough query, however I'm encountering syntax errors and could use some guidance. Essentially I have a function with a text input parameter $1. This parameter can take on 7 different values controlled by the front end of an application.

Depending on the value of this parameter, I need to return a subset of a larger table between a certain range of one of the integer columns returned. To be slightly clearer, the returning query within the function looks something like:

SELECT val_1, val_2, val_3
  FROM schema_name.table_name
 WHERE val_3 BETWEEN
  CASE WHEN $1 = 'a' THEN 0 AND 1
       WHEN $1 = 'b' THEN 2 AND 7
         .
         . 
         .
       WHEN $1 = 'g' THEN 50 AND 100
   END;

However, I'm getting a typically vague error:

ERROR:  syntax error at end of input
LINE 42:    END;
               ^
********** Error **********

ERROR: syntax error at end of input
SQL state: 42601
Character: 1133

The ranges set out in the case statement all differ, with no discernible pattern between them. I could of course write an IF block with 7 ELSE statements essentially writing the same select statement 7 times, but I'm guessing the above can't be too far off. I've never had the need to use a CASE statement in a WHERE clause in this way before. Any hints in the right direction would be greatly appreciated.

Upvotes: 1

Views: 831

Answers (2)

forpas
forpas

Reputation: 164139

A CASE expression returns 1 scalar value and not 2 or an interval of values.
What you can do is use it as a query cross joined to the table:

SELECT t.val_1, t.val_2, t.val_3
FROM schema_name.table_name t
CROSS JOIN (
  SELECT CASE $1 
       WHEN 'a' THEN 0
       WHEN 'b' THEN 1
         .
   END val
) c
WHERE t.val_3 BETWEEN 2 * c.val AND 2 * c.val + 1

Edit.
You can use a CTE which returns all the possible values of $1 and the intervals for each value:

WITH cte(val, min, max) AS (
  VALUES ('a', 0, 1), ('b', 2, 7), .........  
)  
SELECT t.val_1, t.val_2, t.val_3
FROM table_name t 
CROSS JOIN (SELECT * FROM cte WHERE val = $1) c
WHERE t.val_3 BETWEEN c.min AND c.max

Upvotes: 1

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657727

A CASE expression can only return a single value (boolean in my fixed version), not conditional code like you tried:

SELECT val_1, val_2, val_3
FROM   schema_name.table_name
WHERE  CASE $1
         WHEN 'a' THEN val_3 BETWEEN 0 AND 1
         WHEN 'b' THEN val_3 BETWEEN 2 AND 7
          . 
          .
          .
         WHEN 'g' THEN val_3 BETWEEN 50 AND 100
       END;

Using the "simple" form of CASE to make it slightly shorter and faster.

Alternatively, just use boolean logic only:

...
WHERE (
       $1 = 'a' AND val_3 BETWEEN 0 AND 1
   OR  $1 = 'b' AND val_3 BETWEEN 2 AND 7
         . 
         .
         .
   OR  $1 = 'g' AND val_3 BETWEEN 50 AND 100
      )

Parentheses are not strictly needed since operator precedence works in our favor anyway. AND binds before OR and BETWEEN binds before both. But you'll need those parentheses if you add another WHERE condition with AND.

Performance?

For both variants, Postgres 12 (didn't test for earlier versions) is smart enough to still use an index on (val_3) or not, depending on actual column statistics. Even for prepared statements.

If that function is called a lot,
and you work with prepared statements (directly or indirectly),
and performance is critical,
and some of your ranges benefit from an index while others don't
then you might still fork (at least) two distinct queries in order to work with generic, saved query plans for either case to get absolute best performance.

Upvotes: 1

Related Questions