CASE

Goes through conditions in sequence and returns the value of the result associated with the matching condition.

Syntax 1

CASE
    WHEN condition THEN result
    [ ... ]
    [ ELSE result ]
END

Parameters

ParameterDescription
conditionAn expression that evaluates to a BOOLEAN value.
resultA value or an expression that evaluates to a value. The CASE expression returns result if its associated condition evaluates to true.

Returns

  • The result associated with the first condition that evaluates to true. Or,
  • The result in the ELSE clause if no condition evaluates to true. Or,
  • NULL if there is no ELSE clause and no condition evaluates to true.

Syntax 2

CASE expression
    WHEN value THEN result
    [ ... ]
    [ ELSE result ]
END

Parameters

ParameterDescription
expressionAn expression that evaluates to a value. expression is computed in the first place and its value will be compared with value in the WHEN clause.
valueA value or an expression that evaluates to a value. Each value is a potential match for the expression.
resultA value or an expression that evaluates to a value. The CASE expression returns result if its associated value matches the expression.

Returns

  • The result associated with the first value that matches the expression. Or,
  • The result in the ELSE clause if no value matches the expression. Or,
  • NULL if there is no ELSE clause and no value matches the expression.

Example

The following statement (using Syntax 1) classifies the distance of each trip in the table ‘taxi_trips’ into four levels.

SELECT id, distance,
  CASE
    WHEN (distance < 3) THEN 'short'
    WHEN (distance >= 3 AND distance < 10) THEN 'mid'
    WHEN (distance >= 10 AND distance < 20) THEN 'long'
    WHEN (distance >= 20) THEN 'extra'
  END AS "Category"
  FROM taxi_trips;
 id | distance | Category
----+----------+----------
  1 |       16 | long
  2 |       23 | extra
  3 |        6 | mid
  4 |        9 | mid

The following statement (using Syntax 2) classifies the distance of each trip according to its digits.

SELECT id, distance,
  CASE LENGTH (distance::VARCHAR)
    WHEN  1 THEN 'One-digit'
    WHEN  2 THEN 'Double-digit'
    WHEN  3 THEN 'Three-digit'
  END AS Digit
  FROM taxi_trips;
 id | distance |    Digit
----+----------+--------------
  1 |       16 | Double-digit
  2 |       23 | Double-digit
  3 |        6 | One-digit
  4 |        9 | One-digit
(4 rows)

COALESCE

Returns the first non-null value or null if all values are null.

COALESCE ( value [ , ... ] )

NULLIF

Returns null if value1 equals to value2, otherwise returns value1.

NULLIF ( value1, value2 )

greatest

This function returns the largest value in a list of expressions. NULL values in the argument list are ignored. The result will be NULL only if all the expressions evaluate to NULL.

greatest(value1, value2, ...)ANY
greatest(1, 2, 3)3

create table t(id INT, v1 INT2, v2 INT4, v3 INT8);

insert into t values (1, 1, 2, 3), (2, 2, NULL, 5), (3, NULL, NULL, 8), (4, NULL, NULL, NULL);

select greatest(v1, v2, v3) from t order by id;
------ results
3, 5, 8, NULL

least

This function returns the smallest value in a list of expressions. NULL values in the argument list are ignored. The result will be NULL only if all the expressions evaluate to NULL.

least(value1, value2, ...)ANY
create table t(id INT, v1 INT2, v2 INT4, v3 INT8);

insert into t values (1, 1, 2, 3), (2, 2, NULL, 5), (3, NULL, NULL, 8), (4, NULL, NULL, NULL);

select least(1, 2, 3); -- results: 1

select least(v1, v2, v3) from t order by id;
------ results
1, 2, 8, NULL