How to find the size of an array in postgresql

sql arrays postgresql size
aabi picture aabi · Jun 25, 2012 · Viewed 82.9k times · Source

Is there any way to find a size of an array?

For Example,

CREATE TABLE example (id integer[]) ;

INSERT INTO exam VALUES ( '{}');

INSERT INTO exam VALUES ( '{5,6,7}');

From this, is there any possibilities to get a result like following,

size

0

3

Answer

Adam Dingle picture Adam Dingle · May 9, 2015

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;

Related questions

Check if value exists in Postgres array

Using Postgres 9.0, I need a way to test if a value exists in a given array. So far I came up with something like this: select '{1,2,3}'::int[] @> (ARRAY[]::int[] || value_variable::int) But I keep thinking there …

Read More
Store select query's output in one array in postgres

My code is: SELECT column_name FROM information.SCHEMA.columns WHERE table_name = 'aean' It returns column names of table aean. Now I have declared an array: DECLARE colnames text[] How can I store select's output in colnames array. Is …

Read More
PostgreSQL unnest() with element number

When I have a column with separated values, I can use the unnest() function: myTable id | elements ---+------------ 1 |ab,cd,efg,hi 2 |jk,lm,no,pq 3 |rstuv,wxyz select id, unnest(string_to_array(elements, ',')) AS elem …

Read More