Learning PostgreSQL 11 - Third Edition

Common functions of arrays and their operators

Array operators are similar to other data type operators. For example, the = sign is used for equality comparison, and the || operator is used for concatenation. In the previous chapters, we saw some operators similar to &&, which returns true if the arrays are overlapping. Finally, the @> and <@ operators are used if an array contains or is contained by another array, respectively. The unnest function is used to return a set of elements from an array. This is quite useful when you want to use set operations on arrays, such as distinct, order by, intersect, or union. The following example is used to remove the duplicates and sort the array in ascending order:

SELECT array(SELECT DISTINCT unnest ...