PostgreSQL 8.2.6 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

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

```
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

```
COALESCE
```

example given above: ```
SELECT NULLIF(value, '(none)') ...
```

If `
value1
`
is

```
``````
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.