| PostgreSQL 8.1.23 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 languages:
CASE WHEN condition THEN result
[WHEN ...]
[ELSE result]
END
CASE clauses can be used wherever an expression is valid. condition is an expression that returns a boolean result. If the result is true then the value of the CASE expression is the result that follows the condition. If the result is false any subsequent WHEN clauses are searched in the same manner. If no WHEN condition is true then the value of the case expression is the result in the ELSE clause. If the ELSE clause is omitted and no condition matches, the result is null.
An example:
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 detail.
The following "simple" CASE expression is a specialized variant of the general form above:
CASE expression
WHEN value THEN result
[WHEN ...]
[ELSE result]
END
The expression is computed
and compared to all the value
specifications in the WHEN clauses until
one is found that is equal. If no match is found, the
result in 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;
COALESCE(value [, ...])
The 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 will not evaluate arguments that are
not 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
NVL and IFNULL, which are used in some other database
systems.
NULLIF(value1, value2)
The NULLIF function returns a
null value if value1 and
value2 are equal; 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 [, ...])
The GREATEST and 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.
Note that GREATEST and
LEAST are not in the SQL
standard, but are a common extension.
COALESCE is very usefull when we want convert NULL to a empty string.
SELECT COALESCE(NULL,'');
COALESCE doesn't work in subqueries.
This query:
SELECT
a.a_id,
(select coalesce(b,'nothing') FROM table_b b WHERE a.a_id = b.a_id)
FROM table_a a
will give Null if b in Null
to bypass this problem use coalesce outside the subquery like this:
SELECT
a.a_id,
coalesce((select b FROM table_b b WHERE a.a_id = b.a_id),'nothing')
FROM table_a a
this one will give 'nothing' if b is null
The following two examples do NOT yield the same result if b IS NULL and a IS NOT NULL:
IF a > b THEN
b = a;
END IF;
b := greatest(b, a);
As greatest() ignores NULL values, b will be assigned the value of a in the second example. Not so in the first example.
This also applies to SQL 'CASE ..' constructs.
thx1440, I tried your query, and coalesce() DOES work in subqueries.
Try SELECT (SELECT COALESCE(NULL, 1));
Perhaps your subquery returned no results, and you mistook this for a failure of coalesce() ?
thx1440, if a subquery does not return any rows, then a COALESCE within does not get invoked at all.
Your second example catches two different cases:
a.) the value returned by the subquery IS NULL
b.) the subquery returns no row (therfore b IS assigned NULL)
Your first example only catches a.).