Does ARRAY_AGG skip NULLs the way STRING_AGG does, and what do they return for an empty group?

## Two different NULL policies, on purpose SQL's general aggregate rule is that NULL inputs are ignored — the function sees only the non-null values in the group. String aggregation obeys that rule, and array aggregation deliberately does not. Understanding *why* makes both easy to remember. A delimited string is a rendering. If a NULL produced an element, the result would contain an empty slot — `'Rome, , Oslo'` — and a consumer splitting on the delimiter could not tell an absent value from an empty string. So `LISTAGG`, `STRING_AGG` and `GROUP_CONCAT` drop NULL rows entirely: no element, and no separator either, because separators are only placed between the elements that actually exist. An array is a data structure. Its length and its positions carry information: element three of an array of readings is the third reading, and if that reading is missing, dropping it would silently shift everything after it. So `ARRAY_AGG` keeps NULL as a genuine element. Aggregating `('Rome', NULL, 'Oslo')` gives a three-element array whose middle element is NULL, while the string form gives `'Rome, Oslo'` — two elements. Same input, different cardinality. ```sql SELECT string_agg(city, ', ') AS as_text, -- 'Rome, Oslo' array_agg(city) AS as_array -- {Rome,NULL,Oslo} FROM (VALUES ('Rome'), (NULL), ('Oslo')) AS t(city); ``` ## The empty group is a separate case Skipping NULLs and returning NULL for an empty group are different rules that people conflate. If a group contributes rows but every value is NULL, the string aggregate has zero elements to join and returns **NULL** — not an empty string. If the group contributes no rows at all (say, an aggregate over a filter that matches nothing), the result is again NULL, and `ARRAY_AGG` returns NULL too rather than a zero-length array. That last one surprises people: an empty array and NULL are different values, and the aggregate gives you the second. ```sql SELECT array_agg(city) FROM users WHERE 1 = 0; -- one row, containing NULL ``` This has a practical consequence in application code. Client libraries typically map an array-typed NULL to a null reference, not to an empty list, so code that does `for (String c : row.getArray())` will throw rather than loop zero times unless you handle it. The defensive idiom is to substitute at the edge: ```sql SELECT COALESCE(string_agg(city, ', '), '') AS cities_text, COALESCE(array_agg(city), ARRAY[]::text[]) AS cities_array FROM users WHERE active; ``` Note the empty-array literal needs an explicit type in PostgreSQL, because the engine cannot infer an element type from an empty literal. ## Rendering NULLs deliberately Sometimes a NULL *should* appear in the report — "three of these rows have no city" is information. Because the aggregate drops NULLs before it sees them, you cannot recover them afterwards; you have to substitute on the way in: ```sql SELECT country, STRING_AGG(COALESCE(city, '(unknown)'), ', ') WITHIN GROUP (ORDER BY city) AS cities FROM addresses GROUP BY country; ``` Now every row contributes an element and the counts line up with `COUNT(*)`. Without the `COALESCE`, the number of elements in the string equals `COUNT(city)`, not `COUNT(*)` — a cheap mental check when a list looks shorter than expected. ## Why the difference bites Three situations turn this from trivia into a defect. **Positional pairing.** If you aggregate two columns into two arrays intending element *i* of one to correspond to element *i* of the other, and one column has NULLs while the other does not, both arrays stay aligned only because `ARRAY_AGG` keeps NULLs. Do the same with strings and split them client-side, and the two lists have different lengths and silently misalign. **Counting from the rendered list.** Deriving "how many children does this parent have" by splitting the concatenated string undercounts by exactly the number of NULLs. Count with `COUNT(*)` in the same query instead of parsing the string. **Empty versus absent.** Code that treats NULL and `''` as interchangeable will render the literal word `null` in a UI, or produce a CSV cell that is missing rather than empty. Decide at the query boundary which one the caller gets, with `COALESCE`, rather than letting each consumer guess. ## Ordering interacts with NULLs One more detail: if the aggregate's ordering key can be NULL, those rows still participate (the *input* being NULL is what removes a row from the string form, not the sort key). NULL sort keys collapse to one end of the ordering, and where the engine accepts `NULLS FIRST` / `NULLS LAST` in that position you can choose which end — useful when the list is meant to lead with the most recent, dated entries and trail with undated ones.