Is there any way to find a size of an array?
For Example,
CREATE TABLE example (id integer[]) ;
INSERT INTO example VALUES ( '{}');
INSERT INTO example VALUES ( '{5,6,7}');
From this, is there any possibilities to get a result like following,
size
---
0
3
As vyegorov mentioned, array_length will do the trick. Or if you know that the array is 1-dimensional (which is likely) and are running PostgreSQL 9.4 or higher, you can use cardinality:
SELECT cardinality(id) FROM example;
cardinality returns the number of all the elements in a single or multidimensional array. So select cardinality(ARRAY[[1,2], [3,4]]); would return 4, whereas select array_length(ARRAY[[1,2], [3,4]], 1) would return 2. If you're counting the first dimension, array_length is a safer bet.
text type, while function array_length(text[]) does not exist ;)
null and not 0 whereas cardinality returns what you'd expect. No idea what they were thinking with that logic.
cardinality(id) returns 0, but array_length(id,1) returns NULL. So for 1-dimensional arrays cardinality is almost always what you want.It's trivial reading docs:
SELECT array_length(id, 1) FROM example;
array_length. Didn't find that info in docs.
null and 3 instead of 0 and 3 for the OPs example. Definitely should promote use of cardinality when accepting an answer as it's easier to use and less unexpected (I imagine 99.999% use of arrays are single dimensional)Assuming the dimension of the array will always be 1 isn't something I feel comfortable with, so I went with the following:
SELECT coalesce(array_length(id, 1), 0) as size FROM example;
It's been... at least a decade, but we used to do a lot with coalesce and it was pretty handy. Maybe I'm reaching for it out of comfort?
cardinality(id) will also return 0, as the other answer mentionsHad to use array_upper in postgres 8.2.
