How to use conditional expressions-AnalyticDB(AnalyticDB)-阿里云帮助中心

AnalyticDB for PostgreSQL supports the standard PostgreSQL conditional expressions. For the full PostgreSQL reference, see Conditional Expressions.

Limitations

COALESCE, GREATEST, and LEAST resemble functions in syntax but are not ordinary functions. They do not accept the VARIADIC array argument.

CASE

The CASE expression is a general conditional expression, similar to IF/ELSE statements in other programming languages. It evaluates conditions in order and returns the result of the first matching branch.

Syntax

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

Parameters

Parameter	Description
`condition`	A Boolean expression. The first `condition` that evaluates to true determines the returned value.
`result`	The value returned when the corresponding `condition` is true.
`else_result`	The value returned when no `condition` is true. If `ELSE` is omitted and no condition matches, the expression returns NULL.

Example

Given an employees table with name, salary, and bonus columns, calculate each employee's bonus based on their salary range:

SELECT name, salary,
       CASE
           WHEN salary < 30000 THEN salary * 0.05   -- 5% for low salaries
           WHEN salary BETWEEN 30000 AND 50000 THEN salary * 0.10  -- 10% for mid-range
           ELSE salary * 0.15                        -- 15% for high salaries
       END AS bonus_amount
FROM employees;

Expected output (abbreviated):

name   | salary | bonus_amount
-------+--------+------------
Alice  |  25000 |      1250.0
Bob    |  40000 |      4000.0
Carol  |  80000 |     12000.0

COALESCE

COALESCE returns the first non-NULL value in the argument list. Use it to substitute a default value for columns or expressions that may contain NULL. A common use case is returning the first available value from a set of fallback fields — for example, preferring a mobile phone number over a home number over a work number.

Syntax

COALESCE(value1, value2, ..., valueN)

Behavior

Arguments are evaluated left to right.
Evaluation stops at the first non-NULL value, which is returned.
If all arguments are NULL, the expression returns NULL.

Example

Given an employees table where the email column may be NULL, return a fallback address when the email is missing:

SELECT id, name, COALESCE(email, 'no_email@example.com') AS email
FROM employees;

If email is NULL, the expression returns 'no_email@example.com'.
If email is not NULL, the expression returns the stored email value.

NULLIF

NULLIF is the inverse of COALESCE. Where COALESCE replaces NULL with a value, NULLIF converts a specific value back to NULL.

Syntax

NULLIF(expression1, expression2)

Behavior

Returns NULL if expression1 equals expression2.
Returns the value of expression1 if the two expressions are not equal.

Example

Given an orders table with price and quantity columns, calculate the average price per product while avoiding division-by-zero errors:

SELECT product_id,
       SUM(price * quantity) / NULLIF(SUM(quantity), 0) AS average_price
FROM orders
GROUP BY product_id;

If SUM(quantity) equals 0, NULLIF returns NULL, preventing a division-by-zero error.
If SUM(quantity) is not 0, NULLIF returns SUM(quantity) for the division.

GREATEST

GREATEST returns the largest value among the specified arguments. If multiple arguments share the largest value, one of them is returned. If all arguments are NULL, the expression returns NULL.

Unlike some other databases, AnalyticDB for PostgreSQL (following PostgreSQL behavior) only returns NULL when all arguments are NULL. A single NULL argument among non-NULL values is ignored.

Syntax

GREATEST(value1, value2, ..., valueN)

Examples

SELECT GREATEST(10, 20, 30); -- Returns 30
SELECT GREATEST(5, 5, 2);    -- Returns 5
SELECT GREATEST(NULL, 1, 2); -- Returns 2
SELECT GREATEST(NULL, NULL); -- Returns NULL

LEAST

LEAST returns the smallest value among the specified arguments. If multiple arguments share the smallest value, one of them is returned. If all arguments are NULL, the expression returns NULL.

Following PostgreSQL behavior, LEAST only returns NULL when all arguments are NULL. A single NULL argument among non-NULL values is ignored.

Syntax

LEAST(value1, value2, ..., valueN)

Examples

SELECT LEAST(10, 20, 30); -- Returns 10
SELECT LEAST(5, 5, 2);    -- Returns 2
SELECT LEAST(NULL, 1, 2); -- Returns 1
SELECT LEAST(NULL, NULL); -- Returns NULL