24th June 2021:
PostgreSQL 14 Beta 2 Released!

This documentation is for an unsupported version of PostgreSQL.

You may want to view the same page for the current version, or one of the other supported versions listed above instead.

You may want to view the same page for the current version, or one of the other supported versions listed above instead.

PostgreSQL 8.2.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 WHENconditionTHENresult[WHEN ...] [ELSEresult] 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:

CASEexpressionWHENvalueTHENresult[WHEN ...] [ELSEresult] 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.