There are other data types too if you are creating a table using pgAdmin. In this article, you will be learning about how you can find an array length in PostgreSQL in Windows 10 with the help of a few examples.
Syntax
The syntax for finding an array length is:
In the above syntax, the “array_length” returns the length of your desired “column_name” which is written in the first place. The “int” in the second place mentions what dimension of the array is being measured.
Array_Length in PostgreSQL
For finding an array length, you must create a table in PostgreSQL either by using the pgAdmin tool or SQL shell. I have created a table named “Groceries” comprising of three columns. The syntax for creating a table is:
In the above query, as the name suggests, “CREATE TABLE” creates the table with the name of “Groceries” which is composed of three different columns called “ID”, “PERSON”, and “ITEMS”. With each column name, their data types are declared, the “ITEMS” is declared as an array type using [ ], which indicates that we want the data type as an array. Here “ID” is stated as the primary key in a table. After creating the table “Groceries”, the data for each column is inserted according to their respective data types. The “Groceries” table now looks like this
In the “ID” column each person is assigned a number and name in the “PERSON” column. In the “ITEMS” column, those items are mentioned which each person has bought. All items are one-dimensional except the one where ID=7, is expressed as a multidimensional array.
Now that the table is created, let’s run some examples to find the length of an array in the “Groceries” table.
In the above query, the “SELECT” statement is selecting all the columns mentioned including “ID”, “PERSON”, “ITEMS”. The “ARRAY_LENGTH” function which is taking two parameters’ “ITEMS” and “1” means it wants to measure the length of the “ITEMS” column and “1” describes the dimension that array is being measured. The above query shows the following output:
The above output shows the array length of each item a person has bought. As we can see that the data is not in a particular order, the length of 3 items is displayed first, and 1 item is displayed in the end, because the select statement returns the column in an unsorted manner.
Array_Length Using Order By in PostgreSQL
In the previous example, we have seen the length of an array in the result was not sorted. We can sort the column easily using the “Order By” clause either in descending or ascending order by running the following query:
In the above query, the “Order By” clause has sorted the rows in descending order as displayed in the below output:
It can be seen clearly that the “Order By” clause has sorted the “ITEM” column in descending order as we mentioned in the query. Similarly, we can also sort the “ITEMS” column length in ascending order as well, but this time I will be using an alias for the “array_length” column so that I don’t have to repeat it with the “Order By” clause. The query for sorting the column in ascending order is:
In the above query, I have declared the alias as “len_Ascending” and the column is sorted in ascending order which gives the below output:
In the above output, the alias used can be seen and the column is sorted in ascending order by using the “Order By” clause and “ASC” function in the query.
Returning Maximum Array_length in PostgreSQL
If you want only the maximum length of an array in a column, then you can limit the result by using the “LIMIT” clause in your query with the “Order By” clause. The “LIMIT” clause will return only the number of rows you have mentioned. The query for returning only the maximum array length in PostgreSQL is:
In this query, the “Order By” and “Limit” clause returns the maximum array length in the “ITEMS” column. The “Order By” clause and “DESC” function is returning the highest value from the “ITEMS” column and the “LIMIT” clause is returning only the 1 row as mentioned in a query, and the result is displayed as:
The person who bought the maximum number of items is “Catherine” with an array length of 6.
You can also find the person with the minimum grocery items by using the “ASC” function instead of the “DESC” function and can also limit the rows to more than 1.
Array_Length Using Where Clause
If you want to find the array length of a particular person item, then the “Where” clause will help you find that record. “Where” clause filters the rows according to the condition you have set. Below is the query for “array_length” using the “Where” clause:
In the above query, I have specified the condition that finds the array length from the table “Groceries” where the person’s name is Barry, which gives the following result:
The output shows that Barry bought two items which appeared in the last column “array_length”.
Conclusion
We have discussed how we can find the length of an array in PostgreSQL utilizing different examples. PostgreSQL makes it less complicated for using arrays in a query and finding the length of a column using only the simple syntax array_length (column_name, int).
The “array_length” in this syntax returns the length of an array of the first argument i.e., column_name, and “int” tells the dimension of the array measured. Summing up, array length can be measured easily with different functions and clauses as well.
