Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

postgres: use of CASE and ANY() in WHERE clause

Tags:

sql

postgresql

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?

like image 777
Wells Avatar asked Feb 18 '11 18:02

Wells


People also ask

Can I use case in WHERE clause PostgreSQL?

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.

Can case statement be used in WHERE 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.

How do I write a case in PostgreSQL?

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.

What is the purpose of WHERE clause in PostgreSQL?

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.


1 Answers

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

like image 140
jny Avatar answered Sep 17 '22 17:09

jny