{"id":36136,"date":"2024-05-29T11:06:39","date_gmt":"2024-05-29T01:06:39","guid":{"rendered":"https:\/\/database.guide\/?p=36136"},"modified":"2024-06-25T11:52:54","modified_gmt":"2024-06-25T01:52:54","slug":"using-the-array_length-function-in-postgresql","status":"publish","type":"post","link":"https:\/\/database.guide\/using-the-array_length-function-in-postgresql\/","title":{"rendered":"Using the ARRAY_LENGTH() Function in PostgreSQL"},"content":{"rendered":"\n

In PostgreSQL, the array_length()<\/code> function returns the length of the specified array dimension.<\/p>\n\n\n\n

We pass the array as the first argument, and the dimension as the second.<\/p>\n\n\n\n\n\n\n\n

Example<\/h2>\n\n\n\n

Here’s a basic example to demonstrate:<\/p>\n\n\n\n

SELECT array_length(array[ 'Ben', 'Jen', 'Len' ], 1);<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
3<\/pre>\n\n\n\n
Here I passed an array with three elements and so the result is 3<\/code>. <\/p>\n\n\n\n
My second argument was 1<\/code>, because I wanted the length of the first dimension of the array (which in this case happens to be the only dimension).<\/p>\n\n\n\n
Specifying a Non-Existent Dimension<\/h2>\n\n\n\n
If the second argument references a dimension that doesn’t exist, then null<\/code> is returned:<\/p>\n\n\n\n
SELECT array_length(array[ 'Ben', 'Jen', 'Len' ], 2);<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
null<\/pre>\n\n\n\n
In this case I specified 2<\/code> (for the second dimension) but the array only has one dimension and so null<\/code> was returned.<\/p>\n\n\n\n
Multi Dimensional Arrays<\/h2>\n\n\n\n
With multi dimensional arrays, the length will depend on which dimension we specify (and the length of the array at that dimension).<\/p>\n\n\n\n
Here’s a database example:<\/p>\n\n\n\n
SELECT \n    c1,\n    c3,\n    array_length(c3, 1) AS \"1st Dimension\",\n    array_length(c3, 2) AS \"2nd Dimension\",\n    array_length(c3, 3) AS \"3rd Dimension\"\nFROM t1\nORDER BY c1;<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
 c1 |               c3                | 1st Dimension | 2nd Dimension | 3rd Dimension 
----+---------------------------------+---------------+---------------+---------------
  1 | {{1,2,3,4,5,6,7}}               |             1 |             7 |          null
  2 | {{7,8,9},{10,11,12}}            |             2 |             3 |          null
  3 | {1,2,3,4,5}                     |             5 |          null |          null
  4 | {{{1},{2}},{{3},{4}},{{5},{6}}} |             3 |             2 |             1<\/pre>\n\n\n\n
In this example, the database contains arrays in the c3<\/code> column of the t1<\/code> table. I used array_length()<\/code> against that column to get the length of each dimension of each array, up to three dimensions.<\/p>\n\n\n\n
In some cases null<\/code> was returned. This is because the specified dimension didn’t exist in that particular array.<\/p>\n\n\n\n
Arrays Containing null<\/code><\/h2>\n\n\n\n
Any null values in the array are counted just as any other value is:<\/p>\n\n\n\n
SELECT array_length(array[ null, null, null ], 1);<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
3<\/pre>\n\n\n\n
This array contained three null values and so we got a result of 3<\/code>.<\/p>\n\n\n\n
Specifying a null<\/code> Dimension<\/h2>\n\n\n\n
Specifying null<\/code> for the dimension results in null<\/code> being returned:<\/p>\n\n\n\n
SELECT array_length(array[ 'Ben', 'Jen', 'Len' ], null);<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
null<\/pre>\n\n\n\n
<\/p>\n\n\n\n
<\/p>\n\n\n\n
<\/p>\n\n\n\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
In PostgreSQL, the array_length() function returns the length of the specified array dimension. We pass the array as the first argument, and the dimension as the second.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[40],"tags":[169,10,20],"class_list":["post-36136","post","type-post","status-publish","format-standard","hentry","category-postgresql","tag-arrays","tag-how-to","tag-what-is"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/36136","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/comments?post=36136"}],"version-history":[{"count":5,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/36136\/revisions"}],"predecessor-version":[{"id":36619,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/36136\/revisions\/36619"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=36136"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=36136"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=36136"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}