162

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
jayp
  • 192
  • 2
  • 13
aabi
  • 1,731
  • 2
  • 10
  • 4

4 Answers4

230

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;
Adam Dingle
  • 3,114
  • 1
  • 16
  • 14
  • 24
    If anyone can mention the major difference in using array_length and cardinality that would be great – Zia Ul Rehman Mughal Jul 18 '17 at 06:17
  • 21
    `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. – Roshambo Sep 20 '17 at 20:30
  • 8
    This also works for an array saved in a field with `text` type, while `function array_length(text[]) does not exist` ;) – santuxus Sep 17 '18 at 15:17
  • 4
    @ZiaUlRehmanMughal also array length of an empty array unexpectedly evaluates to `null` and not `0` whereas `cardinality` returns what you'd expect. No idea what they were thinking with that logic. – EoghanM Oct 13 '18 at 10:25
  • 2
    I wonder why this is called `cardinality`... Something like `array_size` would be more intuitive IMO – Hiroki Jun 11 '21 at 14:25
  • 1
    **Important difference:** when the array is empty (i.e. length is zero), `cardinality(id)` returns 0, but `array_length(id,1)` returns NULL. So for 1-dimensional arrays cardinality is almost always what you want. – Yarin Aug 07 '22 at 15:13
  • @Hiroki Cardinality is jargon from database theory. Just like the terms Relation and Tupel where a normal person would say Table or Row. – Roland Sep 22 '22 at 15:23
110

It's trivial reading docs:

SELECT array_length(id, 1) FROM example;
vyegorov
  • 21,787
  • 7
  • 59
  • 73
  • 25
    Do you know what is the second parameter of the function `array_length`. Didn't find that info in docs. – suzanshakya Mar 04 '13 at 16:43
  • 27
    @suzanshakya, the length of the requested array _dimension_ ツ – vyegorov Mar 04 '13 at 16:58
  • 13
    This will return `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) – EoghanM Oct 13 '18 at 10:27
  • https://stackoverflow.com/questions/34356546/what-is-the-second-argument-in-array-length-function – sunki Jun 06 '19 at 10:38
20

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?

jc00ke
  • 2,435
  • 1
  • 21
  • 14
7

Had to use array_upper in postgres 8.2.

Sean Anderson
  • 614
  • 8
  • 20