Postgres Pro
Downloads
Home   >   mailing lists

Thread: Multi-dimensional arrays

Multi-dimensional arrays

From
Raymond O'Donnell
Date:
Hello all,

Here's an odd one (to me anyway) which I ran into today.... if I have a
multidimensional array, why does the following return NULL?

    select (array[['abc','def'], ['ghi','jkl']])[1]

I would have expected it to return {abc, def}. This, however, returns
'abc' as expected:

    select (array[['abc','def'], ['ghi','jkl']])[1][1]

Thanks,

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

Re: Multi-dimensional arrays

From
Thom Brown
Date:
On 15 March 2014 12:51, Raymond O'Donnell <rod@iol.ie> wrote:
> Hello all,
>
> Here's an odd one (to me anyway) which I ran into today.... if I have a
> multidimensional array, why does the following return NULL?
>
>     select (array[['abc','def'], ['ghi','jkl']])[1]
>
> I would have expected it to return {abc, def}. This, however, returns
> 'abc' as expected:
>
>     select (array[['abc','def'], ['ghi','jkl']])[1][1]

Hmm... I'm not sure.  You can get to it with slices for some reason:

#  select (array[['abc','def'], ['ghi','jkl']])[0:1];
    array
-------------
 {{abc,def}}
(1 row)

# select (array[['abc','def'], ['ghi','jkl']])[1:2];
         array
-----------------------
 {{abc,def},{ghi,jkl}}
(1 row)

# select (array[['abc','def'], ['ghi','jkl']])[2:3];
    array
-------------
 {{ghi,jkl}}
(1 row)

--
Thom

Re: Multi-dimensional arrays

From
Raymond O'Donnell
Date:
On 15/03/2014 14:01, Thom Brown wrote:
> On 15 March 2014 12:51, Raymond O'Donnell <rod@iol.ie> wrote:
>> Hello all,
>>
>> Here's an odd one (to me anyway) which I ran into today.... if I have a
>> multidimensional array, why does the following return NULL?
>>
>>     select (array[['abc','def'], ['ghi','jkl']])[1]
>>
>> I would have expected it to return {abc, def}. This, however, returns
>> 'abc' as expected:
>>
>>     select (array[['abc','def'], ['ghi','jkl']])[1][1]
>
> Hmm... I'm not sure.  You can get to it with slices for some reason:
>
> #  select (array[['abc','def'], ['ghi','jkl']])[0:1];
>     array
> -------------
>  {{abc,def}}
> (1 row)

True... though that gives you a 2D array, whereas I was hoping for a 1D
array from (array[...])[1]. I should have mentioned my version, by the way:

    select version();

    PostgreSQL 9.3.0, compiled by Visual C++ build 1600, 64-bit


Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

Re: Multi-dimensional arrays

From
Tom Lane
Date:
"Raymond O'Donnell" <rod@iol.ie> writes:
> On 15/03/2014 14:01, Thom Brown wrote:
>> On 15 March 2014 12:51, Raymond O'Donnell <rod@iol.ie> wrote:
>>> Here's an odd one (to me anyway) which I ran into today.... if I have a
>>> multidimensional array, why does the following return NULL?
>>> select (array[['abc','def'], ['ghi','jkl']])[1]

>> Hmm... I'm not sure.  You can get to it with slices for some reason:

It's the wrong number of subscripts.  It's historical I guess that
we return NULL instead of throwing an error.  The general rule is
that a non-slice subscript operation is supposed to return a scalar
(that is, the array element's type), while a slice subscript operation
gives back something of the same array type.  So if you don't put a
colon anywhere, you'd better use two subscripts on this 2-D array.

> True... though that gives you a 2D array, whereas I was hoping for a 1D
> array from (array[...])[1].

Postgres does not think of multi-D arrays as being arrays of arrays.
This is problematic mainly because the SQL standard does think of them
that way.  I'm not sure if there's any hope of changing it though ---
there's probably too much code that would be broken if we did.

            regards, tom lane

Re: Multi-dimensional arrays

From
Thom Brown
Date:
On 15 March 2014 16:21, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Raymond O'Donnell" <rod@iol.ie> writes:
>> True... though that gives you a 2D array, whereas I was hoping for a 1D
>> array from (array[...])[1].
>
> Postgres does not think of multi-D arrays as being arrays of arrays.
> This is problematic mainly because the SQL standard does think of them
> that way.  I'm not sure if there's any hope of changing it though ---
> there's probably too much code that would be broken if we did.

Could we potentially introduce a GUC to get it to behave in a less
surprising way, and eventually make it the default?

--
Thom

Re: Multi-dimensional arrays

From
Tom Lane
Date:
Thom Brown <thom@linux.com> writes:
> On 15 March 2014 16:21, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Postgres does not think of multi-D arrays as being arrays of arrays.
>> This is problematic mainly because the SQL standard does think of them
>> that way.  I'm not sure if there's any hope of changing it though ---
>> there's probably too much code that would be broken if we did.

> Could we potentially introduce a GUC to get it to behave in a less
> surprising way, and eventually make it the default?

GUCs that change application-visible semantics are dangerous; they are
not generally a good way out of tight spots.

            regards, tom lane