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. Also, in 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 one would like to use set operations on arrays, such as distinct, order by, intersect, union, and so on. The following example is used to remove the duplicates and sort the array in an ascending order:
SELECT array(SELECT DISTINCT unnest (array [1,1,1,2,3,3]) ORDER BY 1);
In the preceding example, the result of the unnest function is sorted and duplicates are removed using ORDER BY and DISTINCT, respectively. The array() function is used to construct the array from a set. Also, arrays can be used to aggregate the date, for example, if I would like to get all the models of a certain make, array_agg can be used as follows:
car_portal=> SELECT make, array_agg(model) FROM car_model group by make;
make | array_agg
---------------+-------------------------------------------------------
Volvo | {S80,S60,S50,XC70,XC90}
Audi | {A1,A2,A3,A4,A5,A6,A8}
UAZ | {Patriot}
Citroen | {C1,C2,C3,C4,"C4 Picasso",C5,C6}
The array ANY function is similar to the SQL IN () construct and is used to compare containment, as shown in the following example:
car_portal=> SELECT 1 in (1,2,3), 1 = ANY ('{1,2,3}'::INT[]);
?column? | ?column?
----------+----------
t | t
(1 row)
The full list of arrays functions and operator is quite long, and it can be found in the official documentation https://www.postgresql.org/docs/current/static/functions-array.html. Up till now we have demonstrated several functions including unnest, array_agg, any , array_length, and so on. The following list of functions is often used in daily development:
function |
Return Type |
Description |
Example |
Result |
array_to_string(anyarray, text [, text]) |
text |
convert an array to a text based . One can specify the delimiter as well as NULL value substitution. |
array_to_string(ARRAY[1, NULL, 5], ',', 'x') |
'1,,x,5' |
array_remove(anyarray, anyelement) |
anyarray |
remove all elements based on element value |
array_remove(ARRAY[1,2,3], 2) |
{1,3} |
array_replace(anyarray, anyelement, anyelement) |
anyarray |
replace all array elements equal to the given value with a new value |
array_replace(ARRAY[1,2,5,4], 5, 3) |
{1,2,3,4} |