This is a discussion on can someone explain confusing array indexing nomenclature within the pgsql Sql forums, part of the PostgreSQL category; --> given the following table: protocal2=> select * from sal_emp ; name | pay_by_quarter | schedule -------+---------------------------+------------------------------------------- Bill | {10000,10000,10000,10000} ...
| |||||||
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
| ||||
| 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-ex....html#a8971770 Sent from the PostgreSQL - sql mailing list archive at Nabble.com. ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@postgresql.org so that your message can get through to the mailing list cleanly |
| |||
| 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 |
| |||
| 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-ex....html#a8989242 Sent from the PostgreSQL - sql mailing list archive at Nabble.com. ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings |
| |||
| Στις **μπτη 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 |
| |||
| 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-ex....html#a9009934 Sent from the PostgreSQL - sql mailing list archive at Nabble.com. ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org |
| |||
| Στις *αρασκευή 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 |
| |||
| 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@momjian.us> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@postgresql.org so that your message can get through to the mailing list cleanly |
| ||||
| 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-ex....html#a9138745 Sent from the PostgreSQL - sql mailing list archive at Nabble.com. ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org |