Blog

PostgreSQL Aggregate, Array and Conditional Functions: FILTER, Ordered string_agg/array_agg, unnest, ANY/ALL, COALESCE and GREATEST

In PostgreSQL, FILTER (WHERE ...) gives each aggregate its own condition, and with no rows every aggregate except count returns NULL, not an empty array. Ordered string_agg, array_agg and jsonb_agg, median and percentiles, unnest and array operators, a GIN index that works with @> but not with = ANY, an IN list that is really = ANY, the NULL trap of NOT IN, and how GREATEST treats NULL differently across dialects.