From: Achilleas Mantzios <achill@matrix.gatewaynet.com>
To: pgsql-sql@postgresql.org
Subject: Re: can someone explain confusing array indexing nomenclature
Date: Thu, 15 Feb 2007 12:24:12 +0200
Message-ID: <200702151224.12455.achill@matrix.gatewaynet.com> (raw)
In-Reply-To: <8971770.post@talk.nabble.com>
References: <8971770.post@talk.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: achill@matrix.gatewaynet.com
Subject: Re: can someone explain confusing array indexing nomenclature
In-Reply-To: <200702151224.12455.achill@matrix.gatewaynet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox