Pad arrays with NULL to maximum length for custom aggregate function

Question

From the answer of the question How to use array_agg() for varchar[],

We can create a custom aggregate function to aggregate n-dimensional arrays in Postgres like:

CREATE AGGREGATE array_agg_mult (anyarray)  (
    SFUNC     = array_cat
   ,STYPE     = anyarray
   ,INITCOND  = '{}'
);

A constrain is that the values have to share the same array extents and same length, handling empty values and different lengths doesn't work.

From the answer:

There is no way around that, the array type does not allow such a mismatch in Postgres. You could pad your arrays with NULL values so that all dimensions have matching extents.

I have the rows like

------ arrayfield

-----  {1},
-----  {},
-----  {abc}

array_agg_mult(ARRAY[arrayfield]) AS customarray

I am expecting a aggregate resule like {{1},NULL,{abc}}

but it will throw

ERROR:  cannot concatenate incompatible arrays
DETAIL:  Arrays with differing element dimensions are not compatible for concatenation.

Is there any way that we can add the padding values in the custom function ?

I found the issue is when the array length is different. {a},{null},{1} will aggregate, but {a,b},{},{1} will not.

So I need a query where I can add NULL elements to existing arrays.

One solution is to append two NULL always (2 is the max length going to be in that filed) array_cat(arr, ARRAY[NULL,NULL]) and trim the array to length 2:

   {1}   --> {1,NULL,NULL}     --> {1,NULL}
   {NULL}  --> {NULL,NULL,NULL}  --> {NULL,NULL}
   {abc, def}  --> {abc,def,NULL,NULL}  --> {abc, def}

But I can't figure out the syntax.

I took the liberty to sharpen your question, assuming that's what you're after. — Erwin Brandstetter, Sep 24 '14 at 13:27
Yes , array_dims() or array_ndims() doesn't work for all conditions, can you give the syntax — Sarath, Sep 24 '14 at 13:34
Do you need a padding value in the result, or can we just skip violating values? Can you provide an [SSCCE](http://sscce.org/) demonstrating the desired result? — Erwin Brandstetter, Sep 24 '14 at 14:00
You were on the right track with your last idea. `array_fill()` was the missing tool. — Erwin Brandstetter, Sep 25 '14 at 14:32

score 2 · Accepted Answer · edited May 23 '17 at 10:33

Using the custom aggregate function array_agg_mult() like defined in this related answer:

Selecting data into a Postgres array

Your expected result is impossible:

{{1},NULL,{abc}}

Would have to be:

{{1},{NULL},{abc}}

Simple case with 0 or 1 array elements

For the simple case to just replace the empty array: You can achieve that with:

WITH t(arr) AS (
    VALUES
      ('{1}'::text[])
     ,('{}')
     ,('{abc}')
   )
SELECT array_agg_mult(ARRAY[CASE WHEN arr = '{}' THEN '{NULL}' ELSE arr END])
FROM   t;

Dynamic padding for n elements

Using array_fill() to pad arrays with NULL elements up to the maximum length:

SELECT array_agg_mult(ARRAY[
         arr || array_fill(NULL::text
                         , ARRAY[max_elem - COALESCE(array_length(arr, 1), 0)])
       ]) AS result
FROM   t, (SELECT max(array_length(arr, 1)) AS max_elem FROM t) t1;

Still only works for 1-dimensional basic arrays.

Explain

Subquery t1 computes the maximum length of the basic 1-dimensional array.
COALESCE(array_length(arr, 1), 0) computes the length of the array in this row.
COALESCE defaults to 0 for NULL.
Generate padding array for the difference in length with array_fill().
Append that to arr with ||
Aggregate like above with array_agg_mult().

SQL Fiddle. demonstrating all.
_{Output in SQL Fiddle is misleading, so I cast result to text there.}

Ok, but why there is the same issue when i changed last data to `{abc,efg}` ? — Sarath, Sep 25 '14 at 06:59
@Sarath: dimensions have to match. In that case you'd have to pad all basic arrays with NULL elements up to the `max(array_length(arr, 1))`, so `{1}` becomes `{1, NULL}`, etc. — Erwin Brandstetter, Sep 25 '14 at 07:23
Ok, but even if the condition null wont work in case, like `array_agg_mult(ARRAY[CASE WHEN array_length(arr,1) = 0 THEN '{NULL,NULL}' ELSE arr END])` — Sarath, Sep 25 '14 at 11:51
@Sarath: No, it's a bit more complex than that. Added another solution above. — Erwin Brandstetter, Sep 25 '14 at 14:17

Pad arrays with NULL to maximum length for custom aggregate function

1 Answers1

Simple case with 0 or 1 array elements

Dynamic padding for n elements

Explain

Linked