Обсуждение: array_dims array_lower/upper distance
Wondering if there's a way for postgres to return how many elements are in a array as a single integer? For instance, returning 10 (items in array) instead of [-5:4] Also, is there a way to return the position of an item in a array? __________________________________ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com
On 9/20/05, Matthew Peter <survivedsushi@yahoo.com> wrote: > Wondering if there's a way for postgres to return how > many elements are in a array as a single integer? For > instance, returning 10 (items in array) instead of > [-5:4] > > Also, is there a way to return the position of an item > in a array? > Try using array_upper and specify which array dimension. from http://www.postgresql.org/docs/current/static/arrays.htm: "array_dims produces a text result, which is convenient for people to read but perhaps not so convenient for programs. Dimensions can also be retrieved with array_upper and array_lower, which return the upper and lower bound of a specified array dimension, respectively." pg8=# SELECT array_dims('{4,2,3,5,6}'::INT[]); array_dims ------------ [1:5] (1 row) pg8=# SELECT array_upper('{4,2,3,5,6}'::INT[],1); array_upper ------------- 5
On Wed, 2005-21-09 at 07:48 -0700, Tony Wasson wrote: > On 9/20/05, Matthew Peter <survivedsushi@yahoo.com> wrote: > > Wondering if there's a way for postgres to return how > > many elements are in a array as a single integer? For > > instance, returning 10 (items in array) instead of > > [-5:4] > > > > Also, is there a way to return the position of an item > > in a array? > > > > Try using array_upper and specify which array dimension. > > from http://www.postgresql.org/docs/current/static/arrays.htm: > > "array_dims produces a text result, which is convenient for people to > read but perhaps not so convenient for programs. Dimensions can also > be retrieved with array_upper and array_lower, which return the upper > and lower bound of a specified array dimension, respectively." > > > pg8=# SELECT array_dims('{4,2,3,5,6}'::INT[]); > array_dims > ------------ > [1:5] > (1 row) > > pg8=# SELECT array_upper('{4,2,3,5,6}'::INT[],1); > array_upper > ------------- > 5 > So to answer his question he would likely want : SELECT array_upper(item,1) - array_upper(item,0) + 1 as elements FROM arraytest ;
Guy Fraser <guy@incentre.net> writes: > So to answer his question he would likely want : > > SELECT > array_upper(item,1) - array_upper(item,0) + 1 as elements > FROM > arraytest ; Note that this doesn't work for empty arrays. It will return NULL instead of 0. -- greg
On Thu, 2005-22-09 at 12:43 -0400, Greg Stark wrote: > Guy Fraser <guy@incentre.net> writes: > > > So to answer his question he would likely want : > > > > SELECT > > array_upper(item,1) - array_upper(item,0) + 1 as elements > > FROM > > arraytest ; > > Note that this doesn't work for empty arrays. > It will return NULL instead of 0. Your response was not at all helpfull, I would like to encourage you to expand on what I put off the top of my head. I have not used array_upper() before, and the question was how to return the total number of elements, not how to handle NULL and empty arrays. One could construct a fully logic compliant routine using CASE and IF NULL to generate the type of response you want when checking empty or NULL arrays. If you have something to add then provide details. If you are just trying to seem like you know more than everyone else then don't bother posting.
On Thu, Sep 22, 2005 at 14:16:48 -0600, Guy Fraser <guy@incentre.net> wrote: > On Thu, 2005-22-09 at 12:43 -0400, Greg Stark wrote: > > Guy Fraser <guy@incentre.net> writes: > > > > > So to answer his question he would likely want : > > > > > > SELECT > > > array_upper(item,1) - array_upper(item,0) + 1 as elements > > > FROM > > > arraytest ; > > > > Note that this doesn't work for empty arrays. > > It will return NULL instead of 0. > Your response was not at all helpfull, I would like to > encourage you to expand on what I put off the top of my > head. > > I have not used array_upper() before, and the question was > how to return the total number of elements, not how to > handle NULL and empty arrays. I think his point was that your example was going to give the wrong answer for empty arrays, which is relevant to your question. The normal way around that is to use the COALESCE function.
On Thu, 2005-22-09 at 21:52 -0500, Bruno Wolff III wrote: > On Thu, Sep 22, 2005 at 14:16:48 -0600, > Guy Fraser <guy@incentre.net> wrote: > > On Thu, 2005-22-09 at 12:43 -0400, Greg Stark wrote: > > > Guy Fraser <guy@incentre.net> writes: > > > > > > > So to answer his question he would likely want : > > > > > > > > SELECT > > > > array_upper(item,1) - array_upper(item,0) + 1 as elements > > > > FROM > > > > arraytest ; > > > > > > Note that this doesn't work for empty arrays. > > > It will return NULL instead of 0. > > Your response was not at all helpfull, I would like to > > encourage you to expand on what I put off the top of my > > head. > > > > I have not used array_upper() before, and the question was > > how to return the total number of elements, not how to > > handle NULL and empty arrays. > > I think his point was that your example was going to give the wrong answer > for empty arrays, which is relevant to your question. The normal way around > that is to use the COALESCE function. OK what I jotted down was totally wrong. This is slightly more correct : SELECT array_upper(item,1) - array_lower(item,1) + 1 as elements FROM arraytest ; Without do a tonne of research, I can not refine this to handle all circumstances. Can someone point me to documentation that explains the function better than : Dimensions can also be retrieved with array_upper and array_lower, which return the upper and lower bound of a specified array dimension, respectively. The table "Table 9-36. array Functions" does not explain how empty and null arrays are handled either. How do array_upper() and array_lower() respond to : 1) NULL 2) Empty Array 3) Nonexistent requested dimension Also is there a function that specifies how many dimensions the array has?
--- Guy Fraser <guy@incentre.net> wrote: > On Thu, 2005-22-09 at 21:52 -0500, Bruno Wolff III > wrote: > > On Thu, Sep 22, 2005 at 14:16:48 -0600, > > Guy Fraser <guy@incentre.net> wrote: > > > On Thu, 2005-22-09 at 12:43 -0400, Greg Stark > wrote: > > > > Guy Fraser <guy@incentre.net> writes: > > > > > > > > > So to answer his question he would likely > want : > > > > > > > > > > SELECT > > > > > array_upper(item,1) - array_upper(item,0) + > 1 as elements > > > > > FROM > > > > > arraytest ; > > > > > > > > Note that this doesn't work for empty arrays. > > > > It will return NULL instead of 0. > > > Your response was not at all helpfull, I would > like to > > > encourage you to expand on what I put off the > top of my > > > head. > > > > > > I have not used array_upper() before, and the > question was > > > how to return the total number of elements, not > how to > > > handle NULL and empty arrays. > > > > I think his point was that your example was going > to give the wrong answer > > for empty arrays, which is relevant to your > question. The normal way around > > that is to use the COALESCE function. > OK what I jotted down was totally wrong. > > This is slightly more correct : > > SELECT > array_upper(item,1) - array_lower(item,1) + 1 as > elements > FROM > arraytest ; > > Without do a tonne of research, I can not refine > this to handle > all circumstances. > > Can someone point me to documentation that explains > the function > better than : > > Dimensions can also be retrieved with array_upper > and array_lower, which > return the upper and lower bound of a specified > array dimension, > respectively. > > The table "Table 9-36. array Functions" does not > explain how empty > and null arrays are handled either. > > How do array_upper() and array_lower() respond to : > 1) NULL > 2) Empty Array > 3) Nonexistent requested dimension > > Also is there a function that specifies how many > dimensions the > array has? > That was exactly the answer I was looking for when I posted the question. Now if there was a function to delete a position in the array.... ie set array1 = array_delete_at(array1,5) where 5 is the position to delete __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
On Fri, 2005-23-09 at 09:48 -0700, Matthew Peter wrote: > > --- Guy Fraser <guy@incentre.net> wrote: > ...snip... > > OK what I jotted down was totally wrong. > > > > This is slightly more correct : > > > > SELECT > > array_upper(item,1) - array_lower(item,1) + 1 as > > elements > > FROM > > arraytest ; > > > > Without do a tonne of research, I can not refine > > this to handle > > all circumstances. > > > > Can someone point me to documentation that explains > > the function > > better than : > > > > Dimensions can also be retrieved with array_upper > > and array_lower, which > > return the upper and lower bound of a specified > > array dimension, > > respectively. > > > > The table "Table 9-36. array Functions" does not > > explain how empty > > and null arrays are handled either. > > > > How do array_upper() and array_lower() respond to : > > 1) NULL > > 2) Empty Array > > 3) Nonexistent requested dimension > > > > Also is there a function that specifies how many > > dimensions the > > array has? > > > > That was exactly the answer I was looking for when I > posted the question. Now if there was a function to > delete a position in the array.... > > ie set array1 = array_delete_at(array1,5) where 5 is > the position to delete I hope someone else can answer that, the best I can do is provide a link to the docs : http://www.postgresql.org/docs/current/static/functions-array.html My best guess is that you need to "walk" the array and drop the element you don't want. The way I currently use arrays is I read the whole array into my application the modify the array then update the whole array. Unfortunately the arrays in PG are not associative and the elements must be sequential. I only use arrays in limited ways in PG because of earlier constraints, and have not needed to investigate the newer features. Good luck.
--- Guy Fraser <guy@incentre.net> wrote: > On Fri, 2005-23-09 at 09:48 -0700, Matthew Peter > wrote: > > > > --- Guy Fraser <guy@incentre.net> wrote: > > > ...snip... > > > OK what I jotted down was totally wrong. > > > > > > This is slightly more correct : > > > > > > SELECT > > > array_upper(item,1) - array_lower(item,1) + 1 > as > > > elements > > > FROM > > > arraytest ; > > > > > > Without do a tonne of research, I can not refine > > > this to handle > > > all circumstances. > > > > > > Can someone point me to documentation that > explains > > > the function > > > better than : > > > > > > Dimensions can also be retrieved with > array_upper > > > and array_lower, which > > > return the upper and lower bound of a specified > > > array dimension, > > > respectively. > > > > > > The table "Table 9-36. array Functions" does not > > > explain how empty > > > and null arrays are handled either. > > > > > > How do array_upper() and array_lower() respond > to : > > > 1) NULL > > > 2) Empty Array > > > 3) Nonexistent requested dimension > > > > > > Also is there a function that specifies how many > > > dimensions the > > > array has? > > > > > > > That was exactly the answer I was looking for when > I > > posted the question. Now if there was a function > to > > delete a position in the array.... > > > > ie set array1 = array_delete_at(array1,5) where 5 > is > > the position to delete > > I hope someone else can answer that, the best I can > do > is provide a link to the docs : > > http://www.postgresql.org/docs/current/static/functions-array.html > > My best guess is that you need to "walk" the array > and > drop the element you don't want. The way I currently > > use arrays is I read the whole array into my > application > the modify the array then update the whole array. > > Unfortunately the arrays in PG are not associative > and the > elements must be sequential. I only use arrays in > limited > ways in PG because of earlier constraints, and have > not > needed to investigate the newer features. > > Good luck. > Ya. I read the docs and the (limitedly useful) Douglas book. I'm just playing around with arrays. Michael Fuhr suggested a intarray_del_elem() function. You may want to take a look at it too? MP __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com