array_dims array_lower/upper distance

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