How to exclude null values in array_agg like in string_agg using postgres?

Array tricks: FILTER and array_remove

If you're running PostgreSQL version 9.4 or later, you're in luck. You can wage a two-pronged attack on NULLs using the FILTER and array_remove functions. This double-filtration method ensures cleaner results:

SELECT array_remove(array_agg(val), NULL) FROM tbl; -- "You shall not pass!" - NULLs, probably


Keep in mind, array_remove can come in handy, but using FILTER at the aggregation stage could enhance performance for larger datasets. Always consider your data size when deciding your method of attack.

Mastering null exclusion: More than one way to skin a cat

Diving back in time: PostgreSQL 8.4 approach

If you're dealing with PostgreSQL 8.4, before array functions had their modern powers, use a combo of array_to_string and string_to_array:

SELECT string_to_array(array_to_string(array_agg(val), ','), ',') FROM tbl; -- Vintage filtering


Crafting your own weapon: Custom aggregate function

If you plan on repeating these operations or need to optimize performance, create a custom aggregate function:

CREATE AGGREGATE array_agg_no_nulls (anyelement)
(
    SFUNC = array_append,
    STYPE = anyarray,
    INITCOND = '{}' -- Starting with a clean slate
);


Deploy this function to maintain efficient null exclusion across various queries.

Playing with the big boys: unnest for multi-layered arrays

When dealing with multi-dimensional arrays, unnest them before reassembling:

SELECT array_agg(t.val) FROM (SELECT unnest(array_agg(val)) AS val FROM tbl WHERE val IS NOT NULL) AS t; -- Ever peeled an onion with SQL?


Unnesting first ensures only the crème de la crème make it to the final aggregation.

Deep diving into data integrity

Performance implications: It's not always a walk in the park

Weigh every method's computational cost against its benefits. While the FILTER clause is easy-to-understand, it might strain your CPU for larger datasets. On the contrary, array_remove can pose less stress on the system but can also introduce overhead due to the extra function call.

Type Casting: Never hurts to be strict

Ensure your datatypes align and click in harmony just like a symphony of an orchestra when using array_remove:

SELECT array_remove(array_agg(val::text), NULL) FROM tbl; -- Trust but verify. Declarations matter!


Explicitly casting val to text can avoid potential hiccups caused by PostgreSQL's strict type enforcement.

Paving your own path: Custom solutions

For that extra degree of control and adaptability, consider custom aggregates. Initiate them with an empty array (using the INITCOND parameter), and build it up one non-null brick at a time.