PostgreSQL GREATEST Function

Summary: in this tutorial, you’ll learn how to use the PostgreSQL GREATEST function to select the highest value from a list of values.

Getting Started with the PostgreSQL GREATEST function #

The GREATEST function accepts a list of values and returns the largest value.

Here’s the syntax of the GREATEST function:

GREATEST (expression1, expression2, ....)Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The GREATEST function ignores NULL. It’ll return NULL if all expressions evaluate to NULL.

In SQL standard, the GREATEST function returns NULL if any expression evaluates to NULL. Some databases behave this way like MySQL.

Basic PostgreSQL GREATEST function examples #

The following example uses the GREATEST function to return the highest number in the list:

SELECT GREATEST(1,2,3) highest;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 highest
---------
       3

The GREATEST function returns 3 because it is the highest number in the list.

The following example uses the GREATEST function to return the largest string in alphabetical order:

SELECT GREATEST('A','B','C') largest;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 largest
---------
 C

The GREATEST function returns the letter 'C' is the largest value alphabetically among 'A', 'B', and 'C'.

The following statement uses the GREATEST function to compare two boolean values:

SELECT GREATEST(true, false) largest;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 largest
---------
 t

When comparing true and false, true is greater than false so the GREATEST function returns true.

The following example uses the GREATEST function to find the latest date in a list of date literals:

SELECT
  GREATEST ('2025-01-01', '2025-02-01', '2025-03-01', NULL) latest;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

   latest
------------
 2025-03-01

In this example, the GREATEST function returns the March 1, 2025 because it the latest dates among January 1, 2025, February 1 2025, and March 1, 2025

Notice that The GREATEST function ignores NULL in this example.

Using the PostgreSQL GREATEST function with table data #

We’ll use the products table from the inventory database.

Assuming that the shipping cost is calculated as follows:

$5 per pound.
The minimum shipping fee is $10.

The following statement uses the GREATEST function to ensure at least the minimum fee:

SELECT
  product_name,
  gross_weight,
  GREATEST (gross_weight * 5, 10) AS shipping_cost
FROM
  products
ORDER BY
  product_nameCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

        product_name        | gross_weight | shipping_cost
----------------------------+--------------+---------------
 Apple AirPods Pro 3        |         0.01 |            10
 Apple iMac 24"             |         9.92 |         49.60
 Apple iPad Pro 12.9        |         1.42 |            10
 Apple iPhone 15            |         0.38 |            10
 Apple iPhone 15 Pro Max    |         0.49 |            10
 Apple Watch Series 9       |         0.09 |            10
 Bose SoundLink Max         |         6.61 |         33.05
 Dell Inspiron 27           |        17.64 |         88.20
...

The statement calculates the shipping cost using the product’s gross weight and shipping fee ($5/pound). If the shipping cost is less than 10, then the greatest function returns 10 to ensure the minimum cost.

Summary #

Use the GREATEST function to return the largest value in a list of values.
The GREATEST function ignores NULL.

Quiz #

Was this tutorial helpful ?