Thread: length of array
Hello Is there a function in postgres to return the length of an array field ? I have seen array_dims(array) but this returns a character value. Ideally, I'd like something numeric returned. Thanks Chris
Chris Faulkner wrote: > Is there a function in postgres to return the length of an array field ? I > have seen array_dims(array) but this returns a character value. Ideally, I'd > like something numeric returned. > Afraid not in 7.3.x or earlier. In 7.3.x (but not earlier) you *can* do this (for a one-dimensional array at least): SELECT replace(split_part(array_dims(array_fld),':',1),'[','')::int as low FROM tbl; SELECT replace(split_part(array_dims(array_fld),':',2),']','')::int as high FROM tbl; In 7.4 (now in beta) there are two new functions, array_lower() and array_upper() that do what you're looking for: regression=# select array_lower(array_fld, 1) from tbl; array_lower ------------- 1 (1 row) regression=# select array_upper(array_fld, 1) from tbl; array_upper ------------- 2 (1 row) See the following links for more on 7.4's array support: http://developer.postgresql.org/docs/postgres/arrays.html http://developer.postgresql.org/docs/postgres/functions-array.html http://developer.postgresql.org/docs/postgres/sql-expressions.html#SQL-SYNTAX-ARRAY-CONSTRUCTORS HTH, Joe
Hello Thanks for that solution, Joe - nice use of nested functions ! Related to this problem, I want to constrain a selection using elements of this variable length array. I want to constrain where all elements of the array are 0. I would like to do it like this in Oracle select field from table N where [conditions] and NVL(N.level[1],0) = 0 and NVL(N.level[2],0) = 0 and NVL(N.level[3],0) = 0 and NVL(N.level[4],0) = 0 So if a row only has two elements in the array, but the first two both had values "0", then the row would return. At the moment, I have this : and N.level[1] = 0 and N.level[2] = 0 and N.level[3] = 0 and N.level[4] = 0 but my row with 2 elements in the array won't be returned with this condition. Chris -----Original Message----- From: Joe Conway [mailto:mail@joeconway.com] Sent: 28 August 2003 01:40 To: Chris Faulkner Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] length of array Chris Faulkner wrote: > Is there a function in postgres to return the length of an array field ? I > have seen array_dims(array) but this returns a character value. Ideally, I'd > like something numeric returned. > Afraid not in 7.3.x or earlier. In 7.3.x (but not earlier) you *can* do this (for a one-dimensional array at least): SELECT replace(split_part(array_dims(array_fld),':',1),'[','')::int as low FROM tbl; SELECT replace(split_part(array_dims(array_fld),':',2),']','')::int as high FROM tbl; In 7.4 (now in beta) there are two new functions, array_lower() and array_upper() that do what you're looking for: regression=# select array_lower(array_fld, 1) from tbl; array_lower ------------- 1 (1 row) regression=# select array_upper(array_fld, 1) from tbl; array_upper ------------- 2 (1 row) See the following links for more on 7.4's array support: http://developer.postgresql.org/docs/postgres/arrays.html http://developer.postgresql.org/docs/postgres/functions-array.html http://developer.postgresql.org/docs/postgres/sql-expressions.html#SQL-SYNTA X-ARRAY-CONSTRUCTORS HTH, Joe
Chris Faulkner wrote:
> I would like to do it like this in Oracle
>
> select field from table N where [conditions]
> and NVL(N.level[1],0) = 0
> and NVL(N.level[2],0) = 0
> and NVL(N.level[3],0) = 0
> and NVL(N.level[4],0) = 0
>
> So if a row only has two elements in the array, but the first two both had
> values "0", then the row would return. At the moment, I have this :
>
> and N.level[1] = 0
> and N.level[2] = 0
> and N.level[3] = 0
> and N.level[4] = 0
>
> but my row with 2 elements in the array won't be returned with this
> condition.
Is this what you're looking for?
regression=# select * from t1; id | f1
----+----------- 1 | {1,2} 2 | {0,0,0} 3 | {0,0,0,0} 3 | {1,2,3,0}
(4 rows)
regression=# SELECT * FROM t1 WHERE COALESCE(f1[1],0) = 0 and
COALESCE(f1[2],0) = 0 and COALESCE(f1[3],0) = 0 and COALESCE(f1[4],0) = 0; id | f1
----+----------- 2 | {0,0,0} 3 | {0,0,0,0}
(2 rows)
Joe