Re: can someone explain confusing array indexing nomenclature

given the following table:

protocal2=> select * from sal_emp ;
name | pay_by_quarter | schedule
-------+---------------------------+-------------------------------------------
Bill | {10000,10000,10000,10000} |
{{meeting,lunch},{training,presentation}}
Carol | {20000,25000,25000,25000} |
{{breakfast,consulting},{meeting,lunch}}
(2 rows)

why do the following two queries yield different results??

protocal2=> SELECT schedule[1][2] FROM sal_emp WHERE name = 'Bill';
schedule
----------
lunch
(1 row)

protocal2=> SELECT schedule[1:1][2] FROM sal_emp WHERE name = 'Bill';
schedule
-------------------
{{meeting,lunch}}
(1 row)

--
View this message in context: http://www.nabble.com/can-someone-explain-confusing-array-indexing-nomenclature-tf3229165.html#a8971770
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.

Στις Τετάρτη 14 Φεβρουάριος 2007 21:31, ο/η chrisj έγραψε:
> given the following table:
>
> protocal2=> select * from sal_emp ;
> name | pay_by_quarter | schedule
> -------+---------------------------+---------------------------------------
>---- Bill | {10000,10000,10000,10000} |
> {{meeting,lunch},{training,presentation}}
> Carol | {20000,25000,25000,25000} |
> {{breakfast,consulting},{meeting,lunch}}
> (2 rows)
>
> why do the following two queries yield different results??
>
> protocal2=> SELECT schedule[1][2] FROM sal_emp WHERE name = 'Bill';
> schedule
> ----------
> lunch
> (1 row)
>
> protocal2=> SELECT schedule[1:1][2] FROM sal_emp WHERE name = 'Bill';
> schedule
> -------------------
> {{meeting,lunch}}
> (1 row)

The [n:m] notation denotes a slice of the array (not element).
So schedule[1][2] is the Array element on 2nd col of 1st row,
while schedule[1:1][2] could mean
the second row of the subarray schedule[1:1][1:2].
So these two are foundamentally different things.
In my 7.4 even if you gave
SELECT schedule[1:1][888] FROM sal_emp WHERE name = 'Bill';
you would still get {{meeting,lunch}} as a result.
(Right or wrong is another story).
Anyway the first time you query for a "text",
the second time you query for a "text[]", so you should expect
different results.
--
Achilleas Mantzios

Thanks Achilleas,

I see what you are saying, but if we consider just the index "[2]" for a
moment,
it means something different depending upon the context (in one case it
means "2" and in the other case it means "1:2") and the context is
determined by the format of indexes on other dimensions.

I believe I understand....but incredibly confusing.

- chris

Achilleas Mantzios wrote:
>
> Στις Τετάρτη 14 Φεβρουάριος 2007 21:31, ο/η chrisj έγραψε:
>> given the following table:
>>
>> protocal2=> select * from sal_emp ;
>> name | pay_by_quarter | schedule
>> -------+---------------------------+---------------------------------------
>>---- Bill | {10000,10000,10000,10000} |
>> {{meeting,lunch},{training,presentation}}
>> Carol | {20000,25000,25000,25000} |
>> {{breakfast,consulting},{meeting,lunch}}
>> (2 rows)
>>
>> why do the following two queries yield different results??
>>
>> protocal2=> SELECT schedule[1][2] FROM sal_emp WHERE name = 'Bill';
>> schedule
>> ----------
>> lunch
>> (1 row)
>>
>> protocal2=> SELECT schedule[1:1][2] FROM sal_emp WHERE name = 'Bill';
>> schedule
>> -------------------
>> {{meeting,lunch}}
>> (1 row)
>
> The [n:m] notation denotes a slice of the array (not element).
> So schedule[1][2] is the Array element on 2nd col of 1st row,
> while schedule[1:1][2] could mean
> the second row of the subarray schedule[1:1][1:2].
> So these two are foundamentally different things.
> In my 7.4 even if you gave
> SELECT schedule[1:1][888] FROM sal_emp WHERE name = 'Bill';
> you would still get {{meeting,lunch}} as a result.
> (Right or wrong is another story).
> Anyway the first time you query for a "text",
> the second time you query for a "text[]", so you should expect
> different results.
> --
> Achilleas Mantzios
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
>

--
View this message in context: http://www.nabble.com/can-someone-explain-confusing-array-indexing-nomenclature-tf3229165.html#a8989242
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.

Στις Πέμπτη 15 Φεβρουάριος 2007 18:55, ο/η chrisj έγραψε:
> Thanks Achilleas,
>
> I see what you are saying, but if we consider just the index "[2]" for a
> moment,
> it means something different depending upon the context (in one case it
> means "2" and in the other case it means "1:2") and the context is
> determined by the format of indexes on other dimensions.
>
> I believe I understand....but incredibly confusing.
>

Now that i think about it again, i speculate that the [2] is discarded.

> - chris
>
> Achilleas Mantzios wrote:
> > Στις Ξ�Ρτάρτη 14 ΦΡβρουάριος 2007 21:31, ΞΏ/Ξ· chrisj
έγραψΡ:
> >> given the following table:
> >>
> >> protocal2=> select * from sal_emp ;
> >> name | pay_by_quarter | schedule
> >> -------+---------------------------+------------------------------------
> >>--- ---- Bill | {10000,10000,10000,10000} |
> >> {{meeting,lunch},{training,presentation}}
> >> Carol | {20000,25000,25000,25000} |
> >> {{breakfast,consulting},{meeting,lunch}}
> >> (2 rows)
> >>
> >> why do the following two queries yield different results??
> >>
> >> protocal2=> SELECT schedule[1][2] FROM sal_emp WHERE name = 'Bill';
> >> schedule
> >> ----------
> >> lunch
> >> (1 row)
> >>
> >> protocal2=> SELECT schedule[1:1][2] FROM sal_emp WHERE name = 'Bill';
> >> schedule
> >> -------------------
> >> {{meeting,lunch}}
> >> (1 row)
> >
> > The [n:m] notation denotes a slice of the array (not element).
> > So schedule[1][2] is the Array element on 2nd col of 1st row,
> > while schedule[1:1][2] could mean
> > the second row of the subarray schedule[1:1][1:2].
> > So these two are foundamentally different things.
> > In my 7.4 even if you gave
> > SELECT schedule[1:1][888] FROM sal_emp WHERE name = 'Bill';
> > you would still get {{meeting,lunch}} as a result.
> > (Right or wrong is another story).
> > Anyway the first time you query for a "text",
> > the second time you query for a "text[]", so you should expect
> > different results.
> > --
> > Achilleas Mantzios
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: Have you checked our extensive FAQ?
> >
> > http://www.postgresql.org/docs/faq

--
Achilleas Mantzios

I am quite sure the [2] is not discarded, easy enough to test but I don't
have access to PG at the moment.

Achilleas Mantzios wrote:
>
> Στις Πέμπτη 15 Φεβρουάριος 2007 18:55, ο/η chrisj έγραψε:
>> Thanks Achilleas,
>>
>> I see what you are saying, but if we consider just the index "[2]" for a
>> moment,
>> it means something different depending upon the context (in one case it
>> means "2" and in the other case it means "1:2") and the context is
>> determined by the format of indexes on other dimensions.
>>
>> I believe I understand....but incredibly confusing.
>>
>
> Now that i think about it again, i speculate that the [2] is discarded.
>
>> - chris
>>
>> Achilleas Mantzios wrote:
>> > Στις Ξ�Ρτάρτη 14 ΦΡβρουάριος 2007 21:31, ΞΏ/Ξ·
>> chrisj
> έγραψΡ:
>> >> given the following table:
>> >>
>> >> protocal2=> select * from sal_emp ;
>> >> name | pay_by_quarter | schedule
>> >>
>> -------+---------------------------+------------------------------------
>> >>--- ---- Bill | {10000,10000,10000,10000} |
>> >> {{meeting,lunch},{training,presentation}}
>> >> Carol | {20000,25000,25000,25000} |
>> >> {{breakfast,consulting},{meeting,lunch}}
>> >> (2 rows)
>> >>
>> >> why do the following two queries yield different results??
>> >>
>> >> protocal2=> SELECT schedule[1][2] FROM sal_emp WHERE name = 'Bill';
>> >> schedule
>> >> ----------
>> >> lunch
>> >> (1 row)
>> >>
>> >> protocal2=> SELECT schedule[1:1][2] FROM sal_emp WHERE name = 'Bill';
>> >> schedule
>> >> -------------------
>> >> {{meeting,lunch}}
>> >> (1 row)
>> >
>> > The [n:m] notation denotes a slice of the array (not element).
>> > So schedule[1][2] is the Array element on 2nd col of 1st row,
>> > while schedule[1:1][2] could mean
>> > the second row of the subarray schedule[1:1][1:2].
>> > So these two are foundamentally different things.
>> > In my 7.4 even if you gave
>> > SELECT schedule[1:1][888] FROM sal_emp WHERE name = 'Bill';
>> > you would still get {{meeting,lunch}} as a result.
>> > (Right or wrong is another story).
>> > Anyway the first time you query for a "text",
>> > the second time you query for a "text[]", so you should expect
>> > different results.
>> > --
>> > Achilleas Mantzios
>> >
>> > ---------------------------(end of
>> broadcast)---------------------------
>> > TIP 3: Have you checked our extensive FAQ?
>> >
>> > http://www.postgresql.org/docs/faq
>
> --
> Achilleas Mantzios
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
>

--
View this message in context: http://www.nabble.com/can-someone-explain-confusing-array-indexing-nomenclature-tf3229165.html#a9009934
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.

Στις Παρασκευή 16 Φεβρουάριος 2007 20:35, ο/η chrisj έγραψε:
> I am quite sure the [2] is not discarded, easy enough to test but I don't
> have access to PG at the moment.

Well it should, since

dynacom=# SELECT
(CAST( '{{meeting,lunch},{training,presentation}}' as text[][]))[1:1];
text
-------------------
{{meeting,lunch}}
(1 row)

dynacom=# SELECT
(CAST( '{{meeting,lunch},{training,presentation}}' as text[][]))[1:1][1];
text
-------------
{{meeting}}
(1 row)

dynacom=# SELECT
(CAST( '{{meeting,lunch},{training,presentation}}' as text[][]))[1:1][2];
text
-------------------
{{meeting,lunch}}
(1 row)

dynacom=# SELECT
(CAST( '{{meeting,lunch},{training,presentation}}' as text[][]))[1:1][3];
text
-------------------
{{meeting,lunch}}
(1 row)

dynacom=# SELECT
(CAST( '{{meeting,lunch},{training,presentation}}' as text[][]))[1:1][1000];
text
-------------------
{{meeting,lunch}}
(1 row)

dynacom=#

>
> Achilleas Mantzios wrote:
> > Στις Πέμπτη 15 Φεβρουάριος 2007 18:55, ο/η chrisj έγραψε:
> >> Thanks Achilleas,
> >>
> >> I see what you are saying, but if we consider just the index "[2]" for a
> >> moment,
> >> it means something different depending upon the context (in one case it
> >> means "2" and in the other case it means "1:2") and the context is
> >> determined by the format of indexes on other dimensions.
> >>
> >> I believe I understand....but incredibly confusing.
> >
> > Now that i think about it again, i speculate that the [2] is discarded.
> >
> >> - chris
> >>
> >> Achilleas Mantzios wrote:
> >> > Στις Ξ�Ρτάρτη 14 ΦΡβρουάριος 2007 21:31, ΞΏ/Ξ·
> >>
> >> chrisj
> >
> > έγραψΡ:
> >> >> given the following table:
> >> >>
> >> >> protocal2=> select * from sal_emp ;
> >> >> name | pay_by_quarter | schedule
> >>
> >> -------+---------------------------+------------------------------------
> >>
> >> >>--- ---- Bill | {10000,10000,10000,10000} |
> >> >> {{meeting,lunch},{training,presentation}}
> >> >> Carol | {20000,25000,25000,25000} |
> >> >> {{breakfast,consulting},{meeting,lunch}}
> >> >> (2 rows)
> >> >>
> >> >> why do the following two queries yield different results??
> >> >>
> >> >> protocal2=> SELECT schedule[1][2] FROM sal_emp WHERE name = 'Bill';
> >> >> schedule
> >> >> ----------
> >> >> lunch
> >> >> (1 row)
> >> >>
> >> >> protocal2=> SELECT schedule[1:1][2] FROM sal_emp WHERE name = 'Bill';
> >> >> schedule
> >> >> -------------------
> >> >> {{meeting,lunch}}
> >> >> (1 row)
> >> >
> >> > The [n:m] notation denotes a slice of the array (not element).
> >> > So schedule[1][2] is the Array element on 2nd col of 1st row,
> >> > while schedule[1:1][2] could mean
> >> > the second row of the subarray schedule[1:1][1:2].
> >> > So these two are foundamentally different things.
> >> > In my 7.4 even if you gave
> >> > SELECT schedule[1:1][888] FROM sal_emp WHERE name = 'Bill';
> >> > you would still get {{meeting,lunch}} as a result.
> >> > (Right or wrong is another story).
> >> > Anyway the first time you query for a "text",
> >> > the second time you query for a "text[]", so you should expect
> >> > different results.
> >> > --
> >> > Achilleas Mantzios
> >> >
> >> > ---------------------------(end of
> >>
> >> broadcast)---------------------------
> >>
> >> > TIP 3: Have you checked our extensive FAQ?
> >> >
> >> > http://www.postgresql.org/docs/faq
> >
> > --
> > Achilleas Mantzios
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: Have you checked our extensive FAQ?
> >
> > http://www.postgresql.org/docs/faq

--
Achilleas Mantzios

Yes, it is confusing. I have an update to the array documentation that
should clarify it --- attached.

---------------------------------------------------------------------------

Achilleas Mantzios wrote:
> ???? ????????? 16 ??????????? 2007 20:35, ?/? chrisj ??????:
> > I am quite sure the [2] is not discarded, easy enough to test but I don't
> > have access to PG at the moment.
>
> Well it should, since
>
> dynacom=# SELECT
> (CAST( '{{meeting,lunch},{training,presentation}}' as text[][]))[1:1];
> text
> -------------------
> {{meeting,lunch}}
> (1 row)
>
> dynacom=# SELECT
> (CAST( '{{meeting,lunch},{training,presentation}}' as text[][]))[1:1][1];
> text
> -------------
> {{meeting}}
> (1 row)
>
> dynacom=# SELECT
> (CAST( '{{meeting,lunch},{training,presentation}}' as text[][]))[1:1][2];
> text
> -------------------
> {{meeting,lunch}}
> (1 row)
>
> dynacom=# SELECT
> (CAST( '{{meeting,lunch},{training,presentation}}' as text[][]))[1:1][3];
> text
> -------------------
> {{meeting,lunch}}
> (1 row)
>
> dynacom=# SELECT
> (CAST( '{{meeting,lunch},{training,presentation}}' as text[][]))[1:1][1000];
> text
> -------------------
> {{meeting,lunch}}
> (1 row)
>
> dynacom=#
>
> >
> > Achilleas Mantzios wrote:
> > > ???? ?????? 15 ??????????? 2007 18:55, ?/? chrisj ??????:
> > >> Thanks Achilleas,
> > >>
> > >> I see what you are saying, but if we consider just the index "[2]" for a
> > >> moment,
> > >> it means something different depending upon the context (in one case it
> > >> means "2" and in the other case it means "1:2") and the context is
> > >> determined by the format of indexes on other dimensions.
> > >>
> > >> I believe I understand....but incredibly confusing.
> > >
> > > Now that i think about it again, i speculate that the [2] is discarded.
> > >
> > >> - chris
> > >>
> > >> Achilleas Mantzios wrote:
> > >> > ???????? ?????????????? 14 ?????????????????????? 2007 21:31, ??/??
> > >>
> > >> chrisj
> > >
> > > ????????????:
> > >> >> given the following table:
> > >> >>
> > >> >> protocal2=> select * from sal_emp ;
> > >> >> name | pay_by_quarter | schedule
> > >>
> > >> -------+---------------------------+------------------------------------
> > >>
> > >> >>--- ---- Bill | {10000,10000,10000,10000} |
> > >> >> {{meeting,lunch},{training,presentation}}
> > >> >> Carol | {20000,25000,25000,25000} |
> > >> >> {{breakfast,consulting},{meeting,lunch}}
> > >> >> (2 rows)
> > >> >>
> > >> >> why do the following two queries yield different results??
> > >> >>
> > >> >> protocal2=> SELECT schedule[1][2] FROM sal_emp WHERE name = 'Bill';
> > >> >> schedule
> > >> >> ----------
> > >> >> lunch
> > >> >> (1 row)
> > >> >>
> > >> >> protocal2=> SELECT schedule[1:1][2] FROM sal_emp WHERE name = 'Bill';
> > >> >> schedule
> > >> >> -------------------
> > >> >> {{meeting,lunch}}
> > >> >> (1 row)
> > >> >
> > >> > The [n:m] notation denotes a slice of the array (not element).
> > >> > So schedule[1][2] is the Array element on 2nd col of 1st row,
> > >> > while schedule[1:1][2] could mean
> > >> > the second row of the subarray schedule[1:1][1:2].
> > >> > So these two are foundamentally different things.
> > >> > In my 7.4 even if you gave
> > >> > SELECT schedule[1:1][888] FROM sal_emp WHERE name = 'Bill';
> > >> > you would still get {{meeting,lunch}} as a result.
> > >> > (Right or wrong is another story).
> > >> > Anyway the first time you query for a "text",
> > >> > the second time you query for a "text[]", so you should expect
> > >> > different results.
> > >> > --
> > >> > Achilleas Mantzios
> > >> >
> > >> > ---------------------------(end of
> > >>
> > >> broadcast)---------------------------
> > >>
> > >> > TIP 3: Have you checked our extensive FAQ?
> > >> >
> > >> > http://www.postgresql.org/docs/faq
> > >
> > > --
> > > Achilleas Mantzios
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 3: Have you checked our extensive FAQ?
> > >
> > > http://www.postgresql.org/docs/faq
>
> --
> Achilleas Mantzios
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
> http://www.postgresql.org/about/donate

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

I guess you could say the [2] is discarded since the value "2" is at the top
or beyond the top of the range.

Achilleas Mantzios wrote:
>
> Στις Παρασκευή 16 Φεβρουάριος 2007 20:35, ο/η chrisj έγραψε:
>> I am quite sure the [2] is not discarded, easy enough to test but I don't
>> have access to PG at the moment.
>
> Well it should, since
>
> dynacom=# SELECT
> (CAST( '{{meeting,lunch},{training,presentation}}' as text[][]))[1:1];
> text
> -------------------
> {{meeting,lunch}}
> (1 row)
>
> dynacom=# SELECT
> (CAST( '{{meeting,lunch},{training,presentation}}' as text[][]))[1:1][1];
> text
> -------------
> {{meeting}}
> (1 row)
>
> dynacom=# SELECT
> (CAST( '{{meeting,lunch},{training,presentation}}' as text[][]))[1:1][2];
> text
> -------------------
> {{meeting,lunch}}
> (1 row)
>
> dynacom=# SELECT
> (CAST( '{{meeting,lunch},{training,presentation}}' as text[][]))[1:1][3];
> text
> -------------------
> {{meeting,lunch}}
> (1 row)
>
> dynacom=# SELECT
> (CAST( '{{meeting,lunch},{training,presentation}}' as
> text[][]))[1:1][1000];
> text
> -------------------
> {{meeting,lunch}}
> (1 row)
>
> dynacom=#
>
>>
>> Achilleas Mantzios wrote:
>> > Στις Πέμπτη 15 Φεβρουάριος 2007 18:55, ο/η chrisj έγραψε:
>> >> Thanks Achilleas,
>> >>
>> >> I see what you are saying, but if we consider just the index "[2]" for
>> a
>> >> moment,
>> >> it means something different depending upon the context (in one case
>> it
>> >> means "2" and in the other case it means "1:2") and the context is
>> >> determined by the format of indexes on other dimensions.
>> >>
>> >> I believe I understand....but incredibly confusing.
>> >
>> > Now that i think about it again, i speculate that the [2] is discarded.
>> >
>> >> - chris
>> >>
>> >> Achilleas Mantzios wrote:
>> >> > Στις Ξ�Ρτάρτη 14 ΦΡβρουάριος 2007 21:31, ΞΏ/Ξ·
>> >>
>> >> chrisj
>> >
>> > έγραψΡ:
>> >> >> given the following table:
>> >> >>
>> >> >> protocal2=> select * from sal_emp ;
>> >> >> name | pay_by_quarter | schedule
>> >>
>> >>
>> -------+---------------------------+------------------------------------
>> >>
>> >> >>--- ---- Bill | {10000,10000,10000,10000} |
>> >> >> {{meeting,lunch},{training,presentation}}
>> >> >> Carol | {20000,25000,25000,25000} |
>> >> >> {{breakfast,consulting},{meeting,lunch}}
>> >> >> (2 rows)
>> >> >>
>> >> >> why do the following two queries yield different results??
>> >> >>
>> >> >> protocal2=> SELECT schedule[1][2] FROM sal_emp WHERE name = 'Bill';
>> >> >> schedule
>> >> >> ----------
>> >> >> lunch
>> >> >> (1 row)
>> >> >>
>> >> >> protocal2=> SELECT schedule[1:1][2] FROM sal_emp WHERE name =
>> 'Bill';
>> >> >> schedule
>> >> >> -------------------
>> >> >> {{meeting,lunch}}
>> >> >> (1 row)
>> >> >
>> >> > The [n:m] notation denotes a slice of the array (not element).
>> >> > So schedule[1][2] is the Array element on 2nd col of 1st row,
>> >> > while schedule[1:1][2] could mean
>> >> > the second row of the subarray schedule[1:1][1:2].
>> >> > So these two are foundamentally different things.
>> >> > In my 7.4 even if you gave
>> >> > SELECT schedule[1:1][888] FROM sal_emp WHERE name = 'Bill';
>> >> > you would still get {{meeting,lunch}} as a result.
>> >> > (Right or wrong is another story).
>> >> > Anyway the first time you query for a "text",
>> >> > the second time you query for a "text[]", so you should expect
>> >> > different results.
>> >> > --
>> >> > Achilleas Mantzios
>> >> >
>> >> > ---------------------------(end of
>> >>
>> >> broadcast)---------------------------
>> >>
>> >> > TIP 3: Have you checked our extensive FAQ?
>> >> >
>> >> > http://www.postgresql.org/docs/faq
>> >
>> > --
>> > Achilleas Mantzios
>> >
>> > ---------------------------(end of
>> broadcast)---------------------------
>> > TIP 3: Have you checked our extensive FAQ?
>> >
>> > http://www.postgresql.org/docs/faq
>
> --
> Achilleas Mantzios
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
> http://www.postgresql.org/about/donate
>
>

--
View this message in context: http://www.nabble.com/can-someone-explain-confusing-array-indexing-nomenclature-tf3229165.html#a9138745
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.