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.
Continue readingIn 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.
Continue readingIn PostgreSQL we can use the cardinality()
function to return the total number of elements in an array. This includes elements across all dimensions of the array.
We pass the array as an argument to the function, and it returns an integer of the total number of elements in that array.
Continue readingPostgreSQL has an array_position()
function that returns the position of the first occurrence of a given value in an array.
We have the option to specify a starting position, so that the function returns the position of the first occurrence after that.
To get all occurrences, use array_positions()
instead.
In PostgreSQL, the <@
operator checks to see whether the second array contains the first array. That is, whether or not the array on the right of the operator contains all elements in the array to the left.
The function returns a Boolean result: It returns true
if the second array contains the first array, and false
if it doesn’t. If the result is unknown, it returns NULL
.
In PostgreSQL, the @>
operator checks to see whether the first array contains the second array. That is, whether or not the array on the left of the operator contains all elements in the array to the right.
The function returns a Boolean result: It returns true
if the first array contains the second, and false
if it doesn’t. If the result is unknown, it returns NULL
.
We can use the &&
operator in PostgreSQL to check two arrays for any overlapping elements.
We include an array on each side of the operator to compare them, and the result is a Boolean value that indicates whether or not there’s any overlap. A result of True
(or t
) indicates that there’s an overlap, while False
(or f
) indicates there’s no overlap.
PostgreSQL has an ALL()
construct that we can use when searching for data inside arrays. It returns a Boolean result, which reflects whether the condition is true or not.
In PostgreSQL, we can use the ANY()
construct to perform searches against arrays. It returns a Boolean result, and so we can incorporate this into our searches to return just those rows that yield true or false, as the case may be.
There’s also a SOME()
construct, which is a synonym for ANY()
.
In PostgreSQL, the array_upper()
function returns the upper bound of the specified array dimension. We pass the array as well as the dimension, and it returns the upper bound accordingly.
In PostgreSQL, the array_lower()
function returns the lower bound of the specified array dimension. We pass the array and the relevant dimension, and it returns the lower bound for that dimension.