Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

Common Functions in PostgreSQL

Introduction
The COALESCE() function
The GREATEST() & LEAST() functions
11. The GREATEST() and the LEAST() with NULL
The NULLIF() function
Summary

Instruction

Okay. But what if a value in the list of arguments in GREATEST() or LEAST() functions is NULL? They omit NULL and return the largest or the least values for those different than NULL. The query looks like this:

SELECT
  id,
  LEAST(market2_price, market3_price) AS smallest_price
FROM product;

For the product with ID of 5 it returns 1.99. This comes from the market2_price column because the market3_price stores NULL; therefore it is ignored.

NULL is returned only if all arguments are NULL.

Exercise

For the product with ID of 3 show its name and the largest_price in all three shops.