Thread: Arrays: determining size

Given a variable length array data type in a table:
create table foo (bar text[]);

and some data:
insert into foo values ('{"a","b","c"'});
insert into foo values ('{"1","2","3","4"}');

Is there a simple way to determe the size of each array?

Cheers,
Steve

On Mon, Dec 02, 2002 at 09:58:42AM -0800, Steve Crawford wrote:
> Given a variable length array data type in a table:
> create table foo (bar text[]);
>
> and some data:
> insert into foo values ('{"a","b","c"'});
> insert into foo values ('{"1","2","3","4"}');
>
> Is there a simple way to determe the size of each array?

In contrib/intarray (excellent, BTW), there are features for sizing and
matching integer arrays. Possibly you can either stick with integer
arrays, or adapt the code to non-integer cases.

Would be great if someone would modify all of intarray for text[] -- I'd
love to use the matching language on some projects!

- J.
--

Joel BURTON  |  joel@joelburton.com  |  joelburton.com  |  aim: wjoelburton
Independent Knowledge Management Consultant

Steve,

It worked for me in 7.2.1:

select *, array_dims(bar) from foo;
    bar    | array_dims
-----------+------------
 {1,2,3,4} | [1:4]
 {a,b,c}   | [1:3]
(2 rows)

I think it's two-dimensional; read more in arrays.html in the PostgreSQL
doc.

G.
--
while (!asleep()) sheep++;

---------------------------- cut here ------------------------------
----- Original Message -----
From: "Steve Crawford" <scrawford@pinpointresearch.com>
To: <pgsql-general@postgresql.org>
Sent: Monday, December 02, 2002 6:58 PM
Subject: [GENERAL] Arrays: determining size


> Given a variable length array data type in a table:
> create table foo (bar text[]);
>
> and some data:
> insert into foo values ('{"a","b","c"'});
> insert into foo values ('{"1","2","3","4"}');
>
> Is there a simple way to determe the size of each array?
>
> Cheers,
> Steve
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

Thanks - I had read that document but overlooked array_dims (I had looked for
such functions using \df in psql but array_dims isn't listed). The values
returned are not two-dimensional but rather a start:end element of an array
(see notes below).

Ideally I'd like to get a single int that is the size of the array rather
than the min and max element of the array that array_dims returns but I don't
see a built-in function that does that.

Test notes: by default arrays in Postgresql start with element 1 but they
don't have to so the array dims appear to be the min and max element of the
array. If I add an adjacent element:
update foo set bar[0] = '{"test"}' where id=1;

I get:
select array_dims(bar) from foo;
 [0:2]

Similarly
update foo set bar[-1] = '{"test"}' where id=1;
select array_dims(bar) from foo where id=1;
 [-1:2]

Also, min() and max() seem to report the array dimensions having the smallest
element or the largest element:

select max(array_dims(bar)) from foo;
 [1:2]

select min(array_dims(bar)) from foo;
 [-1:2]

Thanks,
Steve


On Monday 02 December 2002 10:48 am, SZUCS Gábor wrote:
> Steve,
>
> It worked for me in 7.2.1:
>
> select *, array_dims(bar) from foo;
>     bar    | array_dims
> -----------+------------
>  {1,2,3,4} | [1:4]
>  {a,b,c}   | [1:3]
> (2 rows)
>
> I think it's two-dimensional; read more in arrays.html in the PostgreSQL
> doc.
>
> G.

Steve Crawford wrote:
> Thanks - I had read that document but overlooked array_dims (I had looked for
> such functions using \df in psql but array_dims isn't listed). The values
> returned are not two-dimensional but rather a start:end element of an array
> (see notes below).
>
> Ideally I'd like to get a single int that is the size of the array rather
> than the min and max element of the array that array_dims returns but I don't
> see a built-in function that does that.

FWIW, in cvs (7.4devel) there are array_lower() and array_upper() functions,
which return ints. Unfortunately they didn't make it into 7.3.

Joe

Thanks - those will be useful but I can make do for now with array_dims. My
next curiosity (I'll be posting soon when I get a good example) is how
Postgresql deals with elements off the end of the array. Stay tuned :)

-Steve

On Monday 02 December 2002 1:01 pm, Joe Conway wrote:
> Steve Crawford wrote:
> > Thanks - I had read that document but overlooked array_dims (I had looked
> > for such functions using \df in psql but array_dims isn't listed). The
> > values returned are not two-dimensional but rather a start:end element of
> > an array (see notes below).
> >
> > Ideally I'd like to get a single int that is the size of the array rather
> > than the min and max element of the array that array_dims returns but I
> > don't see a built-in function that does that.
>
> FWIW, in cvs (7.4devel) there are array_lower() and array_upper()
> functions, which return ints. Unfortunately they didn't make it into 7.3.
>
> Joe