I l@ve RuBoard

SQL Functions

PostgreSQL includes several functions that return values based on expressions supplied in the current SQL statement. Moreover, these functions are not constrained to acting on specific data types; rather, they act as control structures within a SQL statement.

`CASE WHEN`

Description

The CASE WHEN function is a simple conditional evaluation tool. Most programming languages contain similar constructs. It can be thought of as analogous to the ubiquitous IF…THEN…ELSE statement.

Inputs

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

Example

This example shows a classic IF…THEN…ELSE paradigm in which the CASE WHEN function can be used. The age of an employee is compared against certain constants, and the possible outputs of minor, adult, or unknown are returned depending on their age.

SELECT name, age, 
CASE WHEN age<18 THEN 'minor' 
      WHEN age>=18 THEN 'adult' 
      ELSE 'unknown' 
END 
FROM employees; 
name        age   case 
-------------------
Bill        13    minor 
Timmy       7     minor 
Pam         25    adult 
Barry       NULL  unknown

`COALESCE`

Description

The COALESCE function accepts an arbitrary number of input arguments and returns the first one that is evaluated as NOT NULL. The COALESCE function is very useful for providing display defaults for arbitrary data sources.

Input

COALESCE(arg1, …, argN)

Example

Return a default message to the user:

SELECT COALESCE(book.title, book.description, 'Not Available');

`NULLIF`

Description

The NULLIF function accepts two arguments. It returns a NULL value only if the value of both arguments is equal. Otherwise, it returns the value of the first argument.

Input

NULLIF(arg1, arg2)

Example

In this case, the first value will be returned because the values are not equal:

SELECT NULLIF('hello', 'world'); 

----------------
'hello'

However, when the values are equal, a NULL value is returned:

SELECT NULLIF('hello', SUBSTR('helloword',1,5)); 

NULL

Notes

The NULLIF function behaves in an inverse-like manner of the COALESCE function. It is useful for exception testing, in which a variable is being tested against a known value. If the variable equals the known value, nothing is returned. However, if the values do not match, the value of the evaluated variable is returned instead.

I l@ve RuBoard