BUG #14626: array_agg( anyarray ) unexpected error with multi-valued single-dimension array

david.g.johnston@gmail.com writes:

This doesn't, and should since the number of elements in the non-empty array
shouldn't change the dimensionality logic.

SELECT array_agg(CASE WHEN a = ARRAY[]::text[] THEN ARRAY['N/A']::text[]
ELSE a END)
FROM ( VALUES (1, ARRAY[]::text[]), (1, ARRAY['1','2']::text[]) ) vals (v,
a)

Why do you think that should work? You're asking array_agg to accumulate
a 1-D length-1 array and then a 1-D length-2 array. There's no way to
make a rectangular 2-D array out of that, except perhaps by inventing
entries which isn't in array_agg's charter.

regards, tom lane

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

On Wed, Apr 19, 2017 at 1:03 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

david.g.johnston@gmail.com writes:

This doesn't, and should since the number of elements in the non-empty

array

shouldn't change the dimensionality logic.

SELECT array_agg(CASE WHEN a = ARRAY[]::text[] THEN ARRAY['N/A']::text[]
ELSE a END)
FROM ( VALUES (1, ARRAY[]::text[]), (1, ARRAY['1','2']::text[]) ) vals

(v,

a)

Why do you think that should work? You're asking array_agg to accumulate
a 1-D length-1 array and then a 1-D length-2 array. There's no way to
make a rectangular 2-D array out of that, except perhaps by inventing
entries which isn't in array_agg's charter.

​
I was being too narrow-minded in interpreting the word dimension.​

​SELECT array_ndims(ARRAY['1','2']::text[]); => 1; therefore it is a one
dimensional array - having a length of 2. One can, and we do in other
places, define that array as having a dimension of 1x2.

SELECT '{{N/A},{1,2}}'::text[] --> fails with "sub-arrays with matching
dimensions"...

Apparently the annoyance I posted on the other thread isn't so simple to
resolve - just having an empty "1-dimensional" array is not particularly
useful given the length dimension must also match.

This helps explain why my first attempt:

SELECT array_dims('{1,2}'::text[]); --> yields [1:2] which is what is being
keyed off here.

All this and all I really want is a friggin' "array_append / array_concat"
aggregate function that accepts either scalars or matching "primary
dimension" arrays - and treats empty arrays as no-ops.

David J.

On Wed, Apr 19, 2017 at 2:32 PM, David G. Johnston <
david.g.johnston@gmail.com> wrote:

On Wed, Apr 19, 2017 at 1:03 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

david.g.johnston@gmail.com writes:

This doesn't, and should since the number of elements in the non-empty

array

shouldn't change the dimensionality logic.

SELECT array_agg(CASE WHEN a = ARRAY[]::text[] THEN ARRAY['N/A']::text[]
ELSE a END)
FROM ( VALUES (1, ARRAY[]::text[]), (1, ARRAY['1','2']::text[]) ) vals

(v,

a)

Why do you think that should work? You're asking array_agg to accumulate
a 1-D length-1 array and then a 1-D length-2 array. There's no way to
make a rectangular 2-D array out of that, except perhaps by inventing
entries which isn't in array_agg's charter.

​
All this and all I really want is a friggin' "array_append / array_concat"
aggregate function that accepts either scalars or matching "primary
dimension" arrays - and treats empty arrays as no-ops.

​In the end I realized that serializing the arrays to text would work just
fine. The extra I/O for converting from and to an actual array type is
immaterial in my situation. It still doesn't remove my actual desire for
an aggregate array_concat type function, and the for a rectangular array is
a bit annoying (i.e., allow non-rectangular and just report an
out-of-bounds error on attempts to explicitly access non-existent elements)
but that's easy enough to toss in with the other peculiarities in this
area.

SELECT unnest(array_agg)::text[]
FROM (
SELECT array_agg(a)
FROM ( VALUES (1, ARRAY[]::text[]::text), (1, ARRAY['1','2']::text[]::text)
) vals (v, a)
) txt_arrays;

David J.