Search
K
Links

Conditionals and Filters

Additional flexibility when composing a query

Conditionals and Filters Overview

Conditions are used to define what logic is to be executed based on the status of some condition being satisfied. Filters are expressions used to specify a subset of the data items.

CASE

Case statement. Expressions are evaluated in order, and the first to evaluate to true is returned.
ELSE returns a default value if no statement evaluates to true. If there is no ELSE condition and no case evaluates to true, then NULL is returned.
Operator
CASE
WHEN exprs THEN result1
[ WHEN exprs THEN result2 ]
[ ELSE default ]
END
Example
CASE
WHEN state = 'CA' THEN 1
WHEN score > 500 THEN 1
ELSE 0
END

CASE expression

“Simple” case statement. This is similar to the above, except on checks equality between a common expression and values.
Operator
CASE expression
WHEN value THEN result
[WHEN ...]
[ELSE result]
END
Example
CASE state
WHEN 'CA' THEN 1
WHEN 'TX' THEN 1
ELSE 0
END

WHERE clause

Where clause. Takes a list of filter expressions.
NOTE: we do not currently support subqueries in WHERE clauses.
Operator
WHERE filter_exprs [ AND | OR filter_exprs ]
Example
WHERE
state = 'CA'

FILTER

Filter with a where clause. The where clause only applies to the aggregation preceding the FILTER keyword. Otherwise, the WHERE clause is identical to any other WHERE clause.
Operator
aggregate_function( * ) [ FILTER ( WHERE filter_exprs ) ]
Example
COUNT(*)
FILTER (
WHERE
state = 'CA' OR state = 'TX'
)