From: Achilleas Mantzios <achill@matrix.gatewaynet.com>
Organization: Dynacom Tankers Mgmt
To: pgsql-sql@postgresql.org
Subject: Re: can someone explain confusing array indexing nomenclature
Date: Thu, 15 Feb 2007 12:24:12 +0200
User-Agent: KMail/1.9.3
References: <8971770.post@talk.nabble.com>
In-Reply-To: <8971770.post@talk.nabble.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-7"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Message-Id: <200702151224.12455.achill@matrix.gatewaynet.com>

=D3=F4=E9=F2 =D4=E5=F4=DC=F1=F4=E7 14 =D6=E5=E2=F1=EF=F5=DC=F1=E9=EF=F2 200=
7 21:31, =EF/=E7 chrisj =DD=E3=F1=E1=F8=E5:
> given the following table:
>
> protocal2=3D> select * from sal_emp ;
>  name  |      pay_by_quarter       |                 schedule
> -------+---------------------------+-------------------------------------=
=2D-
>---- 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=3D> SELECT schedule[1][2] FROM sal_emp WHERE name =3D 'Bill';
>  schedule
> ----------
>  lunch
> (1 row)
>
> protocal2=3D> SELECT schedule[1:1][2] FROM sal_emp WHERE name =3D '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,=20
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=20
SELECT schedule[1:1][888] FROM sal_emp WHERE name =3D '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.
=2D-=20
Achilleas Mantzios