CODEWITHSUNDEEP
sqlarrayspostgresqlsize
aabi
aabi

Reputation: 1991

How to find the size of an array in postgresql

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

Upvotes: 188

Views: 258138

Answers (4)

jc00ke
jc00ke

Reputation: 2585

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?

Upvotes: 24

Sean Anderson
Sean Anderson

Reputation: 660

Had to use array_upper in postgres 8.2.

Upvotes: 8

Adam Dingle
Adam Dingle

Reputation: 3602

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;

Upvotes: 268

vyegorov
vyegorov

Reputation: 22895

It's trivial reading docs:

SELECT array_length(id, 1) FROM example;

Upvotes: 124

Related Questions