|PostgreSQL 8.4.19 Documentation|
|Prev||Fast Backward||Chapter 9. Functions and Operators||Fast Forward||Next|
This section describes the SQL-compliant conditional expressions available in PostgreSQL.
Tip: If your needs go beyond the capabilities of these conditional expressions, you might want to consider writing a stored procedure in a more expressive programming language.
The SQL CASE expression is a generic conditional expression, similar to if/else statements in other programming languages:
CASE WHEN condition THEN result [WHEN ...] [ELSE result] END
CASE clauses can be used wherever an expression is valid. Each condition is an expression that returns a boolean result. If the condition's result is true, the value of the CASE expression is the result that follows the condition, and the remainder of the CASE expression is not processed. If the condition's result is not true, any subsequent WHEN clauses are examined in the same manner. If no WHEN condition yields true, the value of the CASE expression is the result of the ELSE clause. If the ELSE clause is omitted and no condition is true, the result is null.
SELECT * FROM test; a --- 1 2 3 SELECT a, CASE WHEN a=1 THEN 'one' WHEN a=2 THEN 'two' ELSE 'other' END FROM test; a | case ---+------- 1 | one 2 | two 3 | other
The data types of all the result expressions must be convertible to a single output type. See Section 10.5 for more details.
There is a "simple" form of CASE expression that is a variant of the general form above:
CASE expression WHEN value THEN result [WHEN ...] [ELSE result] END
The first expression is
computed, then compared to each of the value expressions in the WHEN clauses until one is found that is equal to
it. If no match is found, the result of the ELSE
clause (or a null value) is returned. This is similar to the
switch statement in C.
The example above can be written using the simple CASE syntax:
SELECT a, CASE a WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'other' END FROM test; a | case ---+------- 1 | one 2 | two 3 | other
A CASE expression does not evaluate any subexpressions that are not needed to determine the result. For example, this is a possible way of avoiding a division-by-zero failure:
SELECT ... WHERE CASE WHEN x <> 0 THEN y/x > 1.5 ELSE false END;
Note: As described in Section 34.6, functions and operators marked IMMUTABLE can be evaluated when the query is planned rather than when it is executed. This means that constant parts of a subexpression that is not evaluated during query execution might still be evaluated during query planning.
COALESCE(value [, ...])
COALESCE function returns
the first of its arguments that is not null. Null is returned
only if all arguments are null. It is often used to substitute
a default value for null values when data is retrieved for
display, for example:
SELECT COALESCE(description, short_description, '(none)') ...
Like a CASE expression,
COALESCE only evaluates the arguments that
are needed to determine the result; that is, arguments to the
right of the first non-null argument are not evaluated. This
SQL-standard function provides capabilities similar to
IFNULL, which are used in some other database
NULLIF function returns a
null value if value1 equals
value2; otherwise it returns
value1. This can be used to
perform the inverse operation of the
COALESCE example given above:
SELECT NULLIF(value, '(none)') ...
If value1 is (none), return a null, otherwise return value1.
GREATEST(value [, ...])
LEAST(value [, ...])
LEAST functions select the largest or
smallest value from a list of any number of expressions. The
expressions must all be convertible to a common data type,
which will be the type of the result (see Section 10.5 for details). NULL
values in the list are ignored. The result will be NULL only if
all the expressions evaluate to NULL.
LEAST are not in the SQL
standard, but are a common extension. Some other databases make
them return NULL if any argument is NULL, rather than only when
all are NULL.
Note the subtle difference between SQL CASE expression and the CASE statement in pl/pgsql (since v8.4) With the former it is currently not possible to evaluate a list of expressions in a WHEN clause - as in:
WHEN 1, 2 THEN ...
(Would be nice, though.)
Note that the query-planner does not tend to optimise CASE statements to utilise indexes, and that if you can re-write the query to use a WHERE instead, the query will often be much faster.
#1 SELECT SUM (CASE WHEN id < 10000 THEN 1 ELSE 0 END) FROM test;
#2 SELECT COUNT(1) FROM test WHERE id < 10000;
These queries have the identical result. However, #2 will utilise an index on id, whereas #1 will not. Therefore #1 may run 10-100 times slower.
Note that COALESCE expects its arguments to be of the same type. This can lead to unexpected problems when using it. In PL/pgSQL, you seem to be able to use it the 'wrong' way via variables, until COALESCE needs to evaluate the differently typed arguments.