Re: can someone explain confusing array indexing nomenclature - Mailing list pgsql-sql

From chrisj
Subject Re: can someone explain confusing array indexing nomenclature
Date
Msg-id 9138745.post@talk.nabble.com
Whole thread Raw
In response to Re: can someone explain confusing array indexing nomenclature  (Achilleas Mantzios <achill@matrix.gatewaynet.com>)
List pgsql-sql
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.



pgsql-sql by date:

Previous
From: Geoff Tolley
Date:
Subject: Re: selecting random row values in postgres
Next
From: "Ezequias Rodrigues da Rocha"
Date:
Subject: Change a field to sequence (serial)