PostgreSQL Array Functions and Operators

Below table shows the specialised operators available for array types

Array Operators

anyarray @> anyarray → boolean
Does each element appearing in the second array equal some element of the first array
select array[1,2] @> array[1,2]; → True
select array[1,2] @> array[4,2]; → False
select array[1,2] @> array[3,3]; → False
select array[1,2] @> array[2]; → True
select array[1,1] @> array[1]; → True
select array[1] @> array[1,1]; → True
anyarray <@ anyarray → boolean
Is the first array contained by the second ?
select array[1,2] <@ array[1,2]; → True
select array[1,2] <@ array[4,2]; → False
select array[1,2] <@ array[3,3]; → False
select array[1,2] <@ array[2]; → False
select array[1,1] <@ array[1]; → True
select array[1] <@ array[1,1]; → True
anyarray && anyarray → boolean
Do the arrays overlap, that is, have any elements in common?
select array[1,2] <@ array[1,2]; → True
select array[1,2] <@ array[4,2]; → False
select array[1,2] <@ array[3,3]; → False
select array[1,2] <@ array[2]; → false
select array[1,1] <@ array[1]; → True
select array[1] <@ array[1,1]; → True
anyarray && anyarray → boolean
Do the arrays overlap, that is, have any elements in common?
select array[1,4,3] && array[2,1]; → True
select array[1] && array[2]; → False
anycompatiblearray || anycompatiblearray → anycompatiblearray
Concatenates the two arrays. Concatenating a null or empty array is a no-op; otherwise
the arrays must have the same number of dimensions
select array[1,2,3] || array[4];→ {1,2,3,4}
select array[1,2,3] || array[[4,5,6],[7,8,9.9]]; → {{1,2,3},{4,5,6},{7,8,9.9}}
anycompatible || anycompatiblearray → anycompatiblearray
Concatenates an element onto the front of an array
select 3 || array[4,5]; → {3,4,5}
anycompatiblearray || anycompatible → anycompatiblearray
Concatenates an element onto the end of an array
select array[4,5] || 6 ; → {4,5,6}

Array Functions

array_append ( anycompatiblearray, anycompatible ) → anycompatiblearray
Appends an element to the end of an array
select array_append(array[1,2],3); → {1,2,3}
array_cat ( anycompatiblearray, anycompatiblearray ) → anycompatiblearray
Concatenates two arrays
select array_cat(array[1,2],array[3,4]); → {1,2,3,4}
array_dims ( anyarray ) → text
Returns a text representation of the array’s dimensions
select array_dims(array[1,2]);→ [1:2]
select array_dims(array[[1,2],[3,4]]);→ [1:2][1:2]
array_fill ( anyelement, integer[] [, integer[] ] ) → anyarray
Returns an array filled with copies of the given value, having dimensions of the lengths
specified by the second argument
select array_fill(5,array[2,3]);→ {{5,5,5},{5,5,5}}
array_length ( anyarray, integer ) → integer
Returns the length of the requested array dimension.
select array_length(array[1,2,3],1); → 3
select array_length(array[[1,2,3],[4,5,6]],1); → 2
select array_length(array[[1,2,3],[4,5,6]],2); → 3
array_ndims ( anyarray ) → integer
Returns the number of dimensions of the array
select array_ndims(ARRAY[[1,2,3], [4,5,6]]);→ 2
array_position ( anycompatiblearray, anycompatible [, integer ] ) → integer
Returns the subscript of the first occurrence of the second argument in the array, or
NULL if it’s not present
select array_position(array[‘a’,’b’,’c’,’d’],’c’);→ 3
array_positions ( anycompatiblearray, anycompatible ) → integer[]
Returns an array of the subscripts of all occurrences of the second argument in the array
given as first argument
select array_positions(array[‘A’,’A’,’B’,’A’], ‘A’);→ {1,2,4}
array_prepend ( anycompatible, anycompatiblearray ) → anycompatiblearray
Prepends an element to the beginning of an array
select array_prepend(1, array[2,3]);→ {1,2,3}
array_remove ( anycompatiblearray, anycompatible ) → anycompatiblearray
Removes all elements equal to the given value from the array
select array_remove(array[1,2,3,2], 2); → {1,3}
array_replace ( anycompatiblearray, anycompatible, anycompatible ) →
anycompatiblearray
Replaces each array element equal to the second argument with the third argument
select array_replace(array[1,2,5,4], 5, 3);→ {1,2,3,4}
array_to_string ( array anyarray, delimiter text [, null_string text ] )
→ text
Converts each array element to its text representation, and concatenates those separated
by the delimiter string
select array_to_string(array[1, 2, 3, NULL, 5], ‘,’, ‘*’); → 1,2,3,*,5
array_upper ( anyarray, integer ) → integer
Returns the upper bound of the requested array dimension
select array_upper(array[1,8,3,7], 1); → 4
cardinality ( anyarray ) → integer
Returns the total number of elements in the array, or 0 if the array is empty
select cardinality(array[[1,2],[3,4]]); → 4
trim_array ( array anyarray, n integer ) → anyarray
Trims an array by removing the last n elements. If the array is multidimensional, only the
first dimension is trimmed

select trim_array(array[1,2,3,4,5,6], 2); → {1,2,3,4}
unnest ( anyarray ) → setof anyelement
Expands an array into a set of rows. The array’s elements are read out in storage order
select unnest(array[1,2]);
1
2