Arrays

2011/9/14 Bob Pawley <rjpawley@shaw.ca>

Hi

Is there a method of counting the number of elements in an array??

Yes...

Use function array_upper [1]http://www.postgresql.org/docs/current/interactive/functions-array.html -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL.

See an example:

postgres@bdteste=# SELECT array_upper(ARRAY['foo', 'bar'], 1);
array_upper
-------------
2
(1 row)

[1]: http://www.postgresql.org/docs/current/interactive/functions-array.html -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL
--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL

On Wed, Sep 14, 2011 at 1:05 PM, Fabrízio de Royes Mello
<fabriziomello@gmail.com> wrote:

2011/9/14 Bob Pawley <rjpawley@shaw.ca>

Hi

Is there a method of counting the number of elements in an array??

Yes...
Use function array_upper [1].
See an example:
postgres@bdteste=# SELECT array_upper(ARRAY['foo', 'bar'], 1);
 array_upper
-------------
           2

that only gives you one dimension's worth elements, and only is
correct if the array is 1 based.

select count(*) from unnest(_array_);

will give you an exact count. another way to do it which doesn't
require expanding the array would be to parse and calculate # elements
from the output of array_dims() (which is unfortunately returned as
text).

merlin

On 09/14/2011 11:01 AM, Bob Pawley wrote:

Hi
Is there a method of counting the number of elements in an array??
Bob

Look at array_dims, array_upper and array_lower.

But note that PostgreSQL allows multi-dimensional arrays. The array_dims
function gives you all the dimensions. If you have a one-dimensional
array you can use array_upper(yourarray, 1) and array_lower(yourarray, 2).

Also be aware that the first element in a PostgreSQL array typically has
an index of 1 but does not have to. In fact it is legal to have arrays
that start at a negative index and proceed to a positive one.

Cheers,
Steve

On Wed, Sep 14, 2011 at 21:05, Fabrízio de Royes Mello
<fabriziomello@gmail.com> wrote:

postgres@bdteste=# SELECT array_upper(ARRAY['foo', 'bar'], 1);

On Wed, Sep 14, 2011 at 21:09, Merlin Moncure <mmoncure@gmail.com> wrote:

select count(*) from unnest(_array_);

On Wed, Sep 14, 2011 at 21:15, Steve Crawford
<scrawford@pinpointresearch.com> wrote:

Look at array_dims, array_upper and array_lower.

Huh, what's up with people suggesting overcomplicated solutions?

Just use the one function that's designed to do this: array_length(arr, 1)
Note that for an empty array, this will return NULL. If you want to
get 0 instead, use:
coalesce(array_length(arr, 1), 0)

Note that, for multidimensional arrays, this returns the length of the
1st dimension (hence 1 in arguments)

Regards,
Marti