PostgreSQL GREATEST with NULLs

Using PostgreSQL, I am looking for something like SELECT GREATEST(0,x) where x can be NULL. In case x IS NULL, the query should return NULL, similar to MySQL and Google BigQuery and not 0 which is the standard behavior in PostgreSQL. Is there an easy way to accomplish this without cases and conditions?

SELECT GREATEST(0,NULL) should return NULL, not 0

In the official documentation:

The GREATEST and LEAST functions select the largest or smallest value from a list of any number of expressions. The expressions must all be convertible to a common data type, which will be the type of the result (see Section 10.5 for details). NULL values in the list are ignored. The result will be NULL only if all the expressions evaluate to NULL.

Note that GREATEST and LEAST are not in the SQL standard, but are a common extension. Some other databases make them return NULL if any argument is NULL, rather than only when all are NULL.

I'm looking for a GREATEST function that does not ignore NULLs

3 Answers

You can write your own function:

create function strange_greatest(variadic p_input int[]) returns int as $$ select v from unnest(p_input) as t(v) order by t desc nulls first limit 1; $$ language sql immutable; 

postgres=> select strange_greatest(1,2,4); strange_greatest ---------------- 4 (1 row) postgres=> select strange_greatest(1,2,null,4); strange_greatest ---------------- (1 row) 

You could add another expression. For numbers:

select greatest(a, b, c) + (a + b + c - (a + b + c)) 

This is a bit more challenging for other data types. But arrays can help:

select greatest(a, b, c) * nullif( array_position(array[a, b, c], null) is not null), true )::int 

For the case of 2 numbers (columns), if your table is like this:

create table tablename(a int, b int); insert into tablename(a, b) values (10, 20), (null, 30), (40, null); 

then use the function greatest() like this:

select greatest((a + b) - b, (a + b) - a) "greatest" from tablename; 

If a or b is null then both expressions: (a + b) - b and (a + b) - a are null and the function greatest() will return null.
See the demo.
Results:

| greatest | | -------- | | 20 | | null | | null |