### Get Postgres Tips and Tricks

Subscribe to get advanced Postgres how-tos.

2 The SQL Language : 2.4 Functions and Operators : 2.4.10 Conditional Expressions

2.4.10 Conditional ExpressionsThe following section describes the SQL-compliant conditional expressions available in Advanced Server.2.4.10.1 CASEThe SQL CASE expression is a generic conditional expression, similar to if/else statements in other languages:[ WHEN ... ]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.The data types of all the result expressions must be convertible to a single output type.The following “simple” CASE expression is a specialized variant of the general form above:CASE expression[ WHEN ... ]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.The example above can be written using the simple CASE syntax: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:2.4.10.2 COALESCEThe COALESCE function returns the first of its arguments that is not null. Null is returned only if all arguments are null.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.2.4.10.3 NULLIFThe NULLIF function returns a null value if value1 and value2 are equal; otherwise it returns value1.2.4.10.4 NVLThe NVL function returns the first of its arguments that is not null. NVL evaluates the first expression; if that expression evaluates to NULL, NVL returns the second expression.The return type is the same as the argument types; all arguments must have the same data type (or be coercible to a common type). NVL returns NULL if all arguments are NULL.The following example computes a bonus for non-commissioned employees, If an employee is a commissioned employee, this expression returns the employees commission; if the employee is not a commissioned employee (that is, his commission is NULL), this expression returns a bonus that is 10% of his salary.2.4.10.5 NVL2NVL2 evaluates an expression, and returns either the second or third expression, depending on the value of the first expression. If the first expression is not NULL, NVL2 returns the value in expr2; if the first expression is NULL, NVL2 returns the value in expr3.The following example computes a bonus for commissioned employees - if a given employee is a commissioned employee, this expression returns an amount equal to 110% of his commission; if the employee is not a commissioned employee (that is, his commission is NULL), this expression returns 0.2.4.10.6 GREATEST and LEASTThe GREATEST and LEAST functions select the largest or smallest value from a list of any number of expressions.

2 The SQL Language : 2.4 Functions and Operators : 2.4.10 Conditional Expressions