Array Functions

​

array_distinct

Remove duplicate values from the array x.

Examples:

SELECT array_distinct([1, 2, 3]) as value; -- value '[1, 2, 3]'
SELECT array_distinct([1, 1, 2, 3]) as value; -- value '[1, 2, 3]'

Supported Signatures

function array_distinct(array<$3>) returns array<$3>

🔗 Official Documentation

​

array_except

Returns an array of elements in x but not in y, without duplicates.

Examples:

SELECT array_except([1, 1, 2, 3], [1]) AS value; -- value '[2, 3]'

Supported Signatures

function array_except(array<$3>, array<$3>) returns array<$3>

🔗 Official Documentation

​

array_intersect

Returns an array of the elements in the intersection of x and y, without duplicates.

Examples:

SELECT array_intersect([1, 1, 2, 3], [1]) AS value; -- value '[1]'
SELECT array_intersect(['hello', 'world'], ['hello']) AS value; -- value '[hello]'

Supported Signatures

function array_intersect(array<$3>, array<$3>) returns array<$3>

🔗 Official Documentation

​

array_join

Concatenates the elements of the given array using the delimiter. Null elements are omitted in the result.

Examples:

SELECT array_join(['hello', 'world'], ' beautiful ') AS value; -- value 'hello beautiful world'

Supported Signatures

function array_join(array<$1>, varchar) returns varchar
function array_join(array<$1>, varchar, varchar) returns varchar

🔗 Official Documentation

​

array_max

Returns the maximum value of input array.

Supported Signatures

function array_max(array<$1>) returns $1

🔗 Official Documentation

​

array_min

Returns the minimum value of input array.

Supported Signatures

function array_min(array<$1>) returns $1

🔗 Official Documentation

​

array_position

Returns the position of the first occurrence of the element in array x (or 0 if not found).

Examples:

SELECT array_position([1, 1, 2], 1) AS value; -- value '1'

Supported Signatures

function array_position(array<$1>, $1) returns bigint

🔗 Official Documentation

​

array_remove

Remove all elements that equal element from array x.

Examples:

SELECT array_remove([1, 1, 2], 1) AS value; -- value '[2]'
SELECT array_remove(['hello', 'bad', 'world'], 'bad') AS value; -- value '[hello, world]'

Supported Signatures

function array_remove(array<$3>, $3) returns array<$3>

🔗 Official Documentation

​

array_sort

Sorts and returns the array x. The elements of x must be orderable. Null elements will be placed at the end of the returned array.

Supported Signatures

function array_sort(array<$3>) returns array<$3>

🔗 Official Documentation

​

array_union

Returns an array of the elements in the union of x and y, without duplicates.

Examples:

SELECT array_union([1, 1], [2]) as value; -- value '[1, 2]'

Supported Signatures

function array_union(array<$3>, array<$3>) returns array<$3>

🔗 Official Documentation

​

cardinality

Returns the cardinality (size) of the array x.

Examples:

SELECT cardinality([1, 1, 2]) as value; -- value '3'

Supported Signatures

function cardinality(array<$3>) returns bigint

🔗 Official Documentation

​

contains

Takes an array and an element. Returns true if the array contains the element, false if not.

Examples:

SELECT contains(['hello', 'world'], 'hello') AS value; -- value 'true'
SELECT contains(['hello', 'world'], '😊') AS value; -- value 'false'

Supported Signatures

function contains(array<$1>, $1) returns boolean

🔗 Official Documentation

​

element_at

Returns element of array at given index. If index > 0, this function provides the same functionality as the SQL-standard subscript operator ([]), except that the function returns NULL when accessing an index larger than array length, whereas the subscript operator would fail in such a case. If index < 0, element_at accesses elements from the last to the first.

Supported Signatures

function element_at(array<$3>, bigint) returns $3

🔗 Official Documentation

​

flatten

Flattens an array(array(T)) to an array(T) by concatenating the contained arrays.

Examples:

SELECT flatten(
    ARRAY [ARRAY [1, 2],
    ARRAY [3, 4]])
AS value;
 -- value '[1, 2, 3, 4]'

Supported Signatures

function flatten(array<array<$3>>) returns array<$3>

🔗 Official Documentation

​

repeat

Repeat element for count times.

Examples:

select repeat('😉', 4) as value; -- value '😉😉😉😉'

Supported Signatures

function repeat($1, bigint) returns array<$1>

🔗 Official Documentation