Aggregate strict min and max

The aggregates strict_min and strict_max behave like their built-in counterparts, except they will return NULL if any input is NULL. This implementation is substantially slower than the built-in aggregates, and cannot make use of index support.

Strict min and max aggregates implementation

Snippets

Strict min and max aggregates

Works with PostgreSQL

at least back to 8.4

Written in

SQL

Depends on

Nothing

-- If no values have been delivered to the aggregate, the internal state is the
-- NULL array.  If a null values has been delivered, it is an array with one
-- element, which is NULL.  Otherwise, it is an array with one element,
-- the least/greatest seen to this point.
CREATE OR REPLACE FUNCTION strict_min_agg (anyarray,anyelement )
RETURNS anyarray LANGUAGE sql IMMUTABLE AS $$
        SELECT CASE
            WHEN $1 IS NULL THEN ARRAY[$2]
            WHEN $1[1] IS NULL THEN $1
            WHEN $2 IS NULL THEN ARRAY[$2] -- use $2 not NULL to preserve type
            ELSE ARRAY[least($1[1],$2)] END ;
$$;

CREATE OR REPLACE FUNCTION strict_agg_final (anyarray)
RETURNS anyelement LANGUAGE sql IMMUTABLE AS $$
        SELECT CASE when $1 is null then NULL else $1[1] END ;
$$;

CREATE AGGREGATE strict_min (anyelement) (
        sfunc     = strict_min_agg,
        stype     = anyarray,
        finalfunc = strict_agg_final
);

CREATE OR REPLACE FUNCTION strict_max_agg (anyarray,anyelement )
RETURNS anyarray LANGUAGE sql IMMUTABLE AS $$
        SELECT CASE
            WHEN $1 IS NULL THEN ARRAY[$2]
            WHEN $1[1] IS NULL THEN $1
            WHEN $2 IS NULL THEN ARRAY[$2] -- use $2 not NULL to preserve type
            ELSE ARRAY[greatest($1[1],$2)] END ;
$$;

CREATE AGGREGATE strict_max (anyelement) (
        sfunc     = strict_max_agg,
        stype     = anyarray,
        finalfunc = strict_agg_final
);

parallel enabled implementation

-- for versions 9.6 add two functions and change the aggregates.
CREATE FUNCTION strict_max_combine(anyarray, anyarray) RETURNS anyarray
    LANGUAGE sql IMMUTABLE
    AS $_$
  select case 
  when $1 is null then $2
  when $2 is null then $1
  when $1[1] is null then $1
  when $2[1] is null then $2
  else ARRAY[greatest($1[1],$2[1])] END ;
$_$;

CREATE FUNCTION strict_min_combine(anyarray, anyarray) RETURNS anyarray
    LANGUAGE sql IMMUTABLE
    AS $_$
  select case 
  when $1 is null then $2
  when $2 is null then $1
  when $1[1] is null then $1
  when $2[1] is null then $2
  else ARRAY[least($1[1],$2[1])] END ;
$_$;

CREATE AGGREGATE strict_max(anyelement) (
    SFUNC = strict_max_agg,
    STYPE = anyarray,
    FINALFUNC = strict_agg_final,
    COMBINEFUNC = strict_max_combine,
    PARALLEL = safe
);

CREATE AGGREGATE strict_min(anyelement) (
    SFUNC = strict_min_agg,
    STYPE = anyarray,
    FINALFUNC = strict_agg_final,
    COMBINEFUNC = strict_min_combine,
    PARALLEL = safe
);

Usage

select strict_min(x) from (values (1),(-4),(NULL),(-87)) f(x);

SELECT group_id, strict_max(some_date) FROM t group by group_id;