Re: Ongoing issues with representation of empty arrays

On Mon, Apr 10, 2017 at 4:57 PM, Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk>
wrote:

> The distinction between the standard representation of '{}' as an array
> with zero dimensions and nonstandard representations as a 1-dimensional
> array with zero elements has come up in a couple of contexts on the IRC
> channel recently.
>

​Just to add to the listing of annoyances.

The following makes me want some way, in SQL, to create an empty
1-dimensional array ...

SELECT array_agg(e_arr)
FROM ( VALUES (ARRAY['1']::text[]), (ARRAY[]::text[]) ) v (e_arr);
--ERROR: cannot accumulate arrays of different dimensionality

We moved the goals posts nicely with bac27394a1c but not being able to mix
empty and non-empty arrays is problematic. Ideally an empty array could
become an array of any dimension on-the-fly so that if even explicitly
dimensioned input to array_agg is 1-dimensioned then all empty arrays would
be promoted to 1-dimension and the resultant output would become two
dimensional. unnest'ing such a structure would pose its own challenges
though...

David J.