Is there some way to make this work?
SELECT
*
FROM table t
INNER JOIN othertable t2 USING (tid)
WHERE
t.tid =
CASE
WHEN t2.someboolval THEN ANY(ARRAY[1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16])
ELSE ANY(ARRAY[77,66])
END
Unfortunately I can't just do t.tid = CASE WHEN t2.someboolval THEN 1 ELSE 2 END
because I need to match against an array. Is this doable?
The PostgreSQL CASE expression is the same as IF/ELSE statement in other programming languages. It allows you to add if-else logic to the query to form a powerful query. Since CASE is an expression, you can use it in any places where an expression can be used e.g., SELECT , WHERE , GROUP BY , and HAVING clause.
CASE can be used in any statement or clause that allows a valid expression. For example, you can use CASE in statements such as SELECT, UPDATE, DELETE and SET, and in clauses such as select_list, IN, WHERE, ORDER BY, and HAVING.
Syntax: CASE WHEN boolean-expression-1 THEN statements [ WHEN boolean-expression-2 THEN statements ... ] [ ELSE statements ] END CASE; The searched CASE statement executes statements based on the result of Boolean expressions in each WHEN clause.
The PostgreSQL WHERE clause is used to specify a condition while fetching the data from single table or joining with multiple tables. If the given condition is satisfied, only then it returns specific value from the table.
Use AND/OR. Something like:
SELECT
*
FROM table t
INNER JOIN othertable t2 USING (tid)
WHERE
t2.someboolval AND t.tid IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16)
OR NOT (t2.someboolval) and t.id IN (77,66)
Edit: formatted
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With