In PostgreSQL, conditional expressions are used to select one of the multiple values based on the Boolean condition. The conditional expressions perform the comparison on the given values and return the result based on a Boolean condition. The most frequently used conditional expressions include the CASE expression, COALESCE() function, NULLIF() function, GREATEST() function, and LEAST() function.

This write-up demonstrates a detailed guide on how to work with conditional expressions in PostgreSQL. For a profound understanding, the below-listed topics will be covered in this blog with practical examples:

• How to Use CASE Expression in Postgres?
• What is COALESCE() and How to Use it in Postgres?
• How to Use NULLIF() in Postgres?
• How to Use the GREATEST() and LEAST() Functions in PostgreSQL?

How to Use CASE Expression in Postgres?

CASE is one of the conditional expressions that create conditional queries. It works the same way as the if-else statements do. The below-provided syntax is used to write a CASE expression:

CASE 
    WHEN cond_1  THEN res_1
    WHEN cond_2  THEN res_2
    …
    WHEN cond_N THEN res_N
    ELSE else_result
END

The specified conditions retrieve a Boolean true or false. If a condition specified within the “WHEN” returns true, then the result specified in the respective “THEN” block will execute.

Example: How Does the CASE Statement Work in Postgres?

The below snippet illustrates the data of a sample table named “emp_data”:

Suppose we want to check the employees’ job status, i.e. probation, contract, or permanent:

- If any of the staff members have less than one year of experience then he is on probation.
- If the employee has more than or equal to one year of experience but less than three years then he is on
contract.
- While if the employee has more than or equal to three years of experience then he is permanent.

For this purpose, we will write a CASE expression as follows:

SELECT emp_name,
CASE 
WHEN AGE(current_date, emp_joining_date) < '1 year'
THEN 'Probation'
WHEN AGE(current_date, emp_joining_date) >=  '1 year' AND AGE(current_date, emp_joining_date) < '3 year' 
THEN 'Contract'
WHEN AGE(current_date, emp_joining_date) >=  '3 year' 
THEN 'Permanent'
END CASE
FROM emp_data
ORDER BY emp_id ASC;

In the above query, the AGE() function is used to calculate the employees’ experience:

The output shows that the CASE statement retrieves the result based on the stated conditions.

What is COALESCE() and How to Use it in Postgres?

The COALESCE() function belongs to the category of conditional expressions that retrieves the first non-null value from the given values. It can accept unlimited values as arguments and return only the first non-null value:

COALESCE (arg_1, arg_2, …, arg_n);

It is equivalent to MySQL’s NVL() function and ORACLE’s IFNULL() function. A common use case of the COALESCE() function is to replace the NULL values with some meaningful values.

Example: How Does the COALESCE() Function Work in Postgres?

In the following snippet, we have passed multiple values including the null values to the COALESCE() function:

SELECT COALESCE(NULL, NULL, 19, NULL, 12, 36);

The output shows that the COALESCE() function skips the null values and retrieves the first non-null value.

Note: Read the following article for a profound understanding of Postgres’ COALESCE() function.

How to Use NULLIF() in Postgres?

The NULLIF() function accepts two values as arguments:

• It retrieves NULL if both values are equal.
• It returns the first value if the given values are not equal.
• If any of the given values are NONE, then the NULL value will be retrieved.
• The values to be compared must have a compatible data type.
• The return type of the result value depends on the data type of the first value.

The below-provided syntax is used to employ the NULLIF() function in Postgres:

NULLIF(val_1, val_2);

Let’s understand the working of the NULLIF() function using the following example.

Example: How Does the NULLIF() Function Work in Postgres?

A sample table named “book_info” is created with the following records:

In the following snippet, the NULLIF() function is used on a sample table named “book_info”:

SELECT book_name, best_selling_book, NULLIF(book_name, best_selling_book)
FROM book_info;

The output shows that the NULLIF() function returns NULL when both values are equal, else it returns the first value.

How to Use the GREATEST() and LEAST() Functions in Postgres?

In Postgres, the GREATEST() and LEAST() functions are used to get the greatest and smallest value from the given data. Moreover, these functions are used to compare multiple values at once, making it easy to find the maximum or minimum value among a group of values.

The below-mentioned syntax is used to employ the GREATEST() function in Postgres:

GREATEST(value_1, value_2, value_3, …., value_n);

The stated function will return the greatest value.

The following syntax is used to avail the functionality of the LEAST() function in Postgres:

LEAST(value_1, value_2, value_3, …., value_n);

The stated function will return the smallest value.

Example: How Do the GREATEST() and LEAST() Functions Work in Postgres?

In the following snippet, the GREATEST() function and the LEAST() function accept different dates as arguments:

SELECT GREATEST('2020-10-01', CURRENT_DATE, '2018-12-14'), 
LEAST('2020-10-01', CURRENT_DATE, '2018-12-14');

The output snippet shows that the GREATEST() and LEAST() function returns the greatest and smallest dates, respectively.

Conclusion

In PostgreSQL, conditional expressions are used to select one of the multiple values based on the boolean condition. The most frequently used conditional expressions include the CASE statement, COALESCE() function, NULLIF() function, GREATEST() function, and LEAST() function. The CASE expression works the same as the if-else statements, COALESCE() and NULLIF() functions are used to deal with the NULL values, while the LEAST() and GREATEST() functions are used to find the smallest and greatest values from the given values.