This section describes the SQL-compliant conditional expressions available in PostgreSQL.
Dica: 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 condição THEN resultado [WHEN ...] [ELSE resultado] END
CASE clauses can be used wherever an expression is valid. condição is an expression that returns a boolean result. If the result is true then the value of the CASE expression is the resultado that follows the condition. If the result is false any subsequent WHEN clauses are searched in the same manner. If no WHEN condição is true then the value of the case expression is the resultado 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 resultado expressions must be convertible to a single output type. See Seção 10.5 for more detail.
The following "simple" CASE expression is a specialized variant of the general form above:
CASE expressão WHEN valor THEN resultado [WHEN ...] [ELSE resultado] END
The expressão is computed and compared to all the valor specifications in the WHEN clauses until one is found that is equal. If no match is found, the resultado 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(valor [, ...])
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(valor1, valor2)
The NULLIF function returns a null value if valor1 and valor2 are equal; otherwise it returns valor1. This can be used to perform the inverse operation of the COALESCE example given above:
SELECT NULLIF(value, '(none)') ...
If valor1 is (none), return a null, otherwise return valor1.
GREATEST(valor [, ...])
LEAST(valor [, ...])
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 Seção 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.
