GREATEST() Function in PostgreSQL

In PostgreSQL, the GREATEST() function returns the greatest (or largest) value from a list of any number of expressions.

The GREATEST() function is not included in the SQL standard, but it’s a popular extension that many major RDBMSs support.

Syntax

GREATEST(value [, ...])

This means that we can pass one or more values to the function.

Example

Here’s a simple example to demonstrate:

SELECT GREATEST( 3, 15, 7 );

Result:

Strings

Here’s an example where all arguments are strings:

SELECT GREATEST( 'Bear', 'Zebra', 'Ant' );

Result:

Zebra

Dates

Here’s a comparison of date strings:

SELECT GREATEST(date '2027-01-01', date '2027-12-31');

Result:

2027-12-31

Common Data Type

The expressions must all be convertible to a common data type. The result will use this type.

If the expressions cannot be converted to a common data type, an error occurs:

SELECT GREATEST( 3, 'Fifteen', 7 );

Result:

ERROR:  invalid input syntax for type integer: "Fifteen"
LINE 1: SELECT GREATEST( 3, 'Fifteen', 7 );
                            ^

Null Values

Null values are ignored unless all expressions are null. If all expressions are null, then null is returned:

\pset null '<null>'
SELECT 
    GREATEST( 3, null, 7 ),
    GREATEST( null, null, null );

Result:

 greatest | greatest 
----------+----------
        7 | <null>

By default, psql returns the empty string on null values. In the first line in this example I set null values to output <null> so that it makes it easier for us to see the null result.

Missing Arguments

Calling GREATEST() without any arguments results in an error:

SELECT GREATEST();

Result:

ERROR:  syntax error at or near ")"
LINE 1: SELECT GREATEST();
                        ^

However, we can pass one argument without error:

SELECT GREATEST( 1 );

Result: