How to find the size of an array in postgresql

SqlArraysPostgresqlSize

Sql Problem Overview

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

Sql Solutions

Solution 1 - Sql

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;

Solution 2 - Sql

It's trivial reading docs:

SELECT array_length(id, 1) FROM example;

Solution 3 - Sql

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?

Solution 4 - Sql

Had to use array_upper in postgres 8.2.

Categories
Recommended Sql Solutions
Recommended Arrays Solutions
Recommended Postgresql Solutions
Recommended Size Solutions

Previous Solution:

Java Singleton and Synchronization

JavaMultithreadingSynchronizationSingleton

Next Solution:

Get user profile picture by Id

FacebookFacebook Graph-Api

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionaabiView Question on Stackoverflow
Solution 1 - SqlAdam DingleView Answer on Stackoverflow
Solution 2 - SqlvyegorovView Answer on Stackoverflow
Solution 3 - Sqljc00keView Answer on Stackoverflow
Solution 4 - SqlSean AndersonView Answer on Stackoverflow