How to write CASE WHEN in WHERE Clause in Postgres?

Question

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!

It's generally better to use regular AND/OR instead of case expressions in the WHERE clause. — jarlh
– jarlh, Commented Feb 4, 2022 at 19:09

Joel Coehoorn · Accepted Answer · 2024-11-12 17:20:44Z

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 an if statement.

I know this edit is old now, but it just came into my feed again so people still see it, and today I'd instead look to do this with a JOIN to a VALUES() expression (they're not just for INSERT statements!)

FROM t
INNER JOIN (VALUES 
    ('U', 1), ('U', 2), ('U', 3), 
    ('E', 4), ('E', 5), ('E', 6),
    ('J', 7), ('J', 8), ('J', 9)
) as map(rslt, mpID) ON map.mpID = ${mp_id} AND map.rslt = col1
WHERE date > CAST('${date}' AS TIMESTAMP)
    AND st = 'Y'
    AND RSS = 'wallet'
    AND cob NOT IN (1,2,3,4,5)

This sets me up to eventually factor this out into a real table, which can be indexed and maintained by someone other than a programmer.

jarlh · Accepted Answer · 2022-02-04 19:12:27Z

3

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

answered Feb 4, 2022 at 19:12

jarlh

44.9k8 gold badges52 silver badges68 bronze badges

Collectives™ on Stack Overflow

How to write CASE WHEN in WHERE Clause in Postgres?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related