pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedlength of array
4+ messages / 2 participants
[nested] [flat]
* length of array
@ 2003-08-27 19:49 Chris Faulkner <chrisf@oramap.com>
0 siblings, 1 reply; 4+ messages in thread
From: Chris Faulkner @ 2003-08-27 19:49 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: length of array
@ 2003-08-28 00:40 Joe Conway <mail@joeconway.com>
parent: Chris Faulkner <chrisf@oramap.com>
0 siblings, 1 reply; 4+ messages in thread
From: Joe Conway @ 2003-08-28 00:40 UTC (permalink / raw)
To: Chris Faulkner <chrisf@oramap.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: length of array
@ 2003-08-28 14:37 Chris Faulkner <chrisf@oramap.com>
parent: Joe Conway <mail@joeconway.com>
0 siblings, 1 reply; 4+ messages in thread
From: Chris Faulkner @ 2003-08-28 14:37 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: length of array
@ 2003-08-28 21:27 Joe Conway <mail@joeconway.com>
parent: Chris Faulkner <chrisf@oramap.com>
0 siblings, 0 replies; 4+ messages in thread
From: Joe Conway @ 2003-08-28 21:27 UTC (permalink / raw)
To: Chris Faulkner <chrisf@oramap.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2003-08-28 21:27 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-08-27 19:49 length of array Chris Faulkner <chrisf@oramap.com>
2003-08-28 00:40 ` Joe Conway <mail@joeconway.com>
2003-08-28 14:37 ` Chris Faulkner <chrisf@oramap.com>
2003-08-28 21:27 ` Joe Conway <mail@joeconway.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox