How to write CASE WHEN in WHERE Clause in Postgres?-CodePudding

How to have a CASE WHEN in WHERE clause in PostgresSQL?

I don't want to scan all partitions of a table. And depending on a variable, I want to scan only a specific partition.

SELECT 
t.CUSTOMER_ID
, CASE 
    WHEN t.col1 = 'U' THEN 1
    WHEN t.col1 = 'E' THEN 2
    WHEN t.col1 = 'J' THEN 3
ELSE 0 END AS col1_id
, max(t.date) AS date

FROM t

WHERE 
date > CAST('${date}' AS TIMESTAMP)
    AND st = 'Y'
    and RSS = 'wallet'
    and cob NOT IN (1,2,3,4,5)
    AND CASE 
            WHEN ${mp_id} IN (1,2,3) THEN col1 = 'U'  --this CASE WHEN is not working
            WHEN ${mp_id} IN (4,5,6) THEN col1 = 'E' 
            WHEN ${mp_id} IN (7,8,9) THEN col1 = 'J' 
    END

That CASE WHEN in the WHERE is wrong. I am doing it to only scan the partition 'U' in case the variable ${mp_id} is in (1,2,3) or only scan partition 'E' of the table, if the variable ${mp_id} is in (4,5,6) etc.

How would be the right syntax for that CASE WHEN in the WHERE clause?

Thank you!

CodePudding user response：

Do it like this:

col1 = CASE 
        WHEN ${mp_id} IN (1,2,3) THEN 'U' 
        WHEN ${mp_id} IN (4,5,6) THEN 'E' 
        WHEN ${mp_id} IN (7,8,9) THEN 'J' 
      END

Remember, the CASE keyword is for expressions. It produces a value; it does not select which code to run like if statement.

CodePudding user response：

It's generally better to use regular AND/OR instead of case expressions in the WHERE clause.

WHERE 
date > CAST('${date}' AS TIMESTAMP)
    AND st = 'Y'
    and RSS = 'wallet'
    and cob NOT IN (1,2,3,4,5)
    AND ((${mp_id} IN (1,2,3) AND col1 = 'U') OR
         (${mp_id} IN (4,5,6) AND col1 = 'E') OR 
         (${mp_id} IN (7,8,9) AND col1 = 'J'))