## Re: can someone explain confusing array indexing nomenclature

From: Achilleas Mantzios pgsql-sql(at)postgresql(dot)org Re: can someone explain confusing array indexing nomenclature 2007-02-15 10:24:12 200702151224.12455.achill@matrix.gatewaynet.com (view raw, whole thread or download thread mbox) 2007-02-14 19:31:38 from chrisj  2007-02-15 10:24:12 from Achilleas Mantzios   2007-02-15 16:55:58 from chrisj    2007-02-16 08:34:42 from Achilleas Mantzios     2007-02-16 18:35:13 from chrisj      2007-02-20 07:42:17 from Achilleas Mantzios       2007-02-20 14:26:01 from Bruce Momjian       2007-02-24 23:04:51 from chrisj pgsql-sql
```Στις Τετάρτη 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

```

### pgsql-sql by date

 Next: From: Walter Cruz Date: 2007-02-15 11:19:28 Subject: "for SELECT DISTINCT, ORDER BY expressions must appear in select list" - is that the standart or a limitation of postgresql? Previous: From: Bruno Wolff III Date: 2007-02-15 04:02:52 Subject: Re: problem with join