Aggregate array of array

Question

I have a table with array values.

create table A (v int[]);

insert into A (v) values ('{1,2,3}');
insert into A (v) values ('{}');
insert into A (v) values (NULL);
insert into A (v) values ('{4,5}');

And table for aggregating this values

create table B (v int[][]);

I need insert all rows from a to b so b.v should be

{{123},{},{},{4,5}}

How you see, also I need map null to empty array. I tired something like

insert into B (v) 
select array_agg(v) from A

But it's not worked - Can't aggreate empty or null values.

astentx · Accepted Answer · 2023-05-05 12:12:12Z

As described in the documentation:

Multidimensional arrays must have matching extents for each dimension. A mismatch causes an error, for example:
INSERT INTO sal_emp
    VALUES ('Bill',
    '{10000, 10000, 10000, 10000}',
    '{{"meeting", "lunch"}, {"meeting"}}');
ERROR:  multidimensional arrays must have array expressions with matching dimensions

As a solution based on this question you may

Use custom domain of array type:
```
create domain int_array int[]
```
Use it in your target table:
```
create table b(val int_array[])
```

Create custom aggregate for this type:

create aggregate array_array_agg(int_array) (
  sfunc = array_append,
  stype = int_array[],
  initcond = '{}'
)

And use this custom aggregate:

insert into b(val)
select array_array_agg(v)
from a

INSERT 0 1

select *
from b

val
{"{1,2,3}","{}",NULL,"{4,5}"}

fiddle

Collectives™ on Stack Overflow

Aggregate array of array

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related