Hologres:Conditional functions

Sample data

The examples for CASE, NULLIF, GREATEST, and LEAST use a test table with a single integer column.

CREATE TABLE test (
    a INT
);
INSERT INTO test (a) VALUES (1);
INSERT INTO test (a) VALUES (2);
INSERT INTO test (a) VALUES (3);

CASE

CASE evaluates conditions in order and returns the result for the first condition that is true. Two syntax forms are available.

Searched form — each branch tests an independent Boolean expression:

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

Simple form — compares a single expression against a list of values (similar to a switch statement):

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

Example (searched form)

SELECT a,
  CASE WHEN a=1 THEN 'one'
       WHEN a=2 THEN 'two'
       ELSE 'other'
  END
FROM test;

Result:

a  | case
---+-------
1  | one
2  | two
3  | other

COALESCE

COALESCE returns the first non-null value from a list of expressions. It is commonly used to substitute a default value when a column might be null.

Example

-- Returns 1 (first non-null value)
SELECT COALESCE(1, 2);

-- Returns 2 (first argument is null)
SELECT COALESCE(NULL, 2, 1);

-- Returns 0 (first argument is null)
SELECT COALESCE(NULL, 0);

GREATEST

GREATEST returns the largest value from a list of expressions.

Example

SELECT a,
      GREATEST('a', 'b', 'c')
FROM test;

Result:

a  | greatest
---+---------
1  | c
2  | c
3  | c

IF

IF returns one of two values based on a condition.

IF(condition, value_if_true, value_if_false)

Behavior by condition result

SELECT IF(TRUE,  'yes', 'no');   -- Returns 'yes'
SELECT IF(FALSE, 'yes', 'no');   -- Returns 'no'
SELECT IF(NULL,  'yes', 'no');   -- Returns 'no'

Example

DROP TABLE IF EXISTS if_test;
CREATE TABLE if_test (
    id   int,
    name text,
    age  int
);

INSERT INTO if_test VALUES
    ('1', 'a', '18'), ('2', 'b', '19'), ('3', 'c', '25'),
    ('4', 'd', '8'),  ('5', 'e', '27'), ('6', 'f', '21');

SELECT
    name,
    IF(age >= 18, 'Adult', 'Minor')
FROM if_test;

Result:

name | if
-----+-------
a    | Adult
c    | Adult
e    | Adult
f    | Adult
d    | Minor
b    | Adult

LEAST

LEAST returns the smallest value from a list of expressions.

Example

SELECT a,
      LEAST('a', 'b', 'c')
FROM test;

Result:

a  | least
---+------
1  | a
2  | a
3  | a

NULLIF

NULLIF compares two expressions. If they are equal, it returns null. If they are different, it returns the first expression.

NULLIF is the inverse of COALESCE: use it to convert a known placeholder value (such as an empty string or a sentinel like '(none)') back to null before passing it to COALESCE.

Example

SELECT a,
      NULLIF('a', 'a')
FROM test;

Result:

a  | nullif
---+-------
1  |
2  |
3  |

Both arguments are 'a', so NULLIF returns null for every row.

References

For the full PostgreSQL specification, see Conditional expressions.