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
– Erwin Brandstetter, Commented Sep 24, 2014 at 13:27
Yes , array_dims() or array_ndims() doesn't work for all conditions, can you give the syntax — Sarath
– Sarath, Commented Sep 24, 2014 at 13:34
Do you need a padding value in the result, or can we just skip violating values? Can you provide an SSCCE demonstrating the desired result? — Erwin Brandstetter
– Erwin Brandstetter, Commented Sep 24, 2014 at 14:00
You were on the right track with your last idea. array_fill() was the missing tool. — Erwin Brandstetter
– Erwin Brandstetter, Commented Sep 25, 2014 at 14:32

Community · Accepted Answer · 2017-05-23 10:33:23Z

2

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.}

edited May 23, 2017 at 10:33

CommunityBot

11 silver badge

answered Sep 24, 2014 at 14:31

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Sarath Over a year ago

Ok, but why there is the same issue when i changed last data to {abc,efg} ?

Erwin Brandstetter Over a year ago

@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.

Sarath Over a year ago

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])

Erwin Brandstetter Over a year ago

@Sarath: No, it's a bit more complex than that. Added another solution above.

Collectives™ on Stack Overflow

Pad arrays with NULL to maximum length for custom aggregate function

1 Answer 1

Simple case with 0 or 1 array elements

Dynamic padding for n elements

Explain

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Simple case with 0 or 1 array elements

Dynamic padding for n elements

Explain

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related