'How to write CASE WHEN in WHERE Clause in Postgres?

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!

sqlpostgresql


Solution 1:[1]

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'))

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 jarlh

Related Questions