LEAST() Function in PostgreSQL

In PostgreSQL, the LEAST() function returns the smallest value from a list of any number of expressions.

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

Syntax

LEAST(value [, ...])

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

Example

Here’s a simple example to demonstrate:

SELECT LEAST( 5, 2, 9 );

Result:

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 LEAST( 5, 'Two', 9 );

Result:

ERROR:  invalid input syntax for type integer: "Two"
LINE 1: SELECT LEAST( 5, 'Two', 9 );
                         ^

Strings

The previous example is not to suggest that we can’t uses strings. It’s merely to show that we can’t convert data types.

To demonstrate, here’s an example where all arguments are strings:

SELECT LEAST( 'Cat', 'Dog', 'Aardvark' );

Result:

Aardvark

Dates

Here’s a comparison of date strings:

SELECT LEAST(date '2030-01-01', date '2030-12-31');

Result:

2030-01-01

Null Values

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

\pset null '<null>'
SELECT 
    LEAST( 5, null, 9 ),
    LEAST( null, null, null );

Result:

 least | least  
-------+--------
     5 | <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 LEAST() without any arguments results in an error:

SELECT LEAST();

Result:

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

However, we can pass one argument without error:

SELECT LEAST( 1 );

Result: