<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>9.18. Array Functions and Operators</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="functions-conditional.html" title="9.17. Conditional Expressions" /><link rel="next" href="functions-range.html" title="9.19. Range Functions and Operators" /></head><body><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">9.18. Array Functions and Operators</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="functions-conditional.html" title="9.17. Conditional Expressions">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="functions.html" title="Chapter 9. Functions and Operators">Up</a></td><th width="60%" align="center">Chapter 9. Functions and Operators</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 11.5 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="functions-range.html" title="9.19. Range Functions and Operators">Next</a></td></tr></table><hr></hr></div><div class="sect1" id="FUNCTIONS-ARRAY"><div class="titlepage"><div><div><h2 class="title" style="clear: both">9.18. Array Functions and Operators</h2></div></div></div><p>
<a class="xref" href="functions-array.html#ARRAY-OPERATORS-TABLE" title="Table 9.48. Array Operators">Table 9.48</a> shows the operators
available for array types.
</p><div class="table" id="ARRAY-OPERATORS-TABLE"><p class="title"><strong>Table 9.48. Array Operators</strong></p><div class="table-contents"><table class="table" summary="Array Operators" border="1"><colgroup><col /><col /><col /><col /></colgroup><thead><tr><th>Operator</th><th>Description</th><th>Example</th><th>Result</th></tr></thead><tbody><tr><td> <code class="literal">=</code> </td><td>equal</td><td><code class="literal">ARRAY[1.1,2.1,3.1]::int[] = ARRAY[1,2,3]</code></td><td><code class="literal">t</code></td></tr><tr><td> <code class="literal"><></code> </td><td>not equal</td><td><code class="literal">ARRAY[1,2,3] <> ARRAY[1,2,4]</code></td><td><code class="literal">t</code></td></tr><tr><td> <code class="literal"><</code> </td><td>less than</td><td><code class="literal">ARRAY[1,2,3] < ARRAY[1,2,4]</code></td><td><code class="literal">t</code></td></tr><tr><td> <code class="literal">></code> </td><td>greater than</td><td><code class="literal">ARRAY[1,4,3] > ARRAY[1,2,4]</code></td><td><code class="literal">t</code></td></tr><tr><td> <code class="literal"><=</code> </td><td>less than or equal</td><td><code class="literal">ARRAY[1,2,3] <= ARRAY[1,2,3]</code></td><td><code class="literal">t</code></td></tr><tr><td> <code class="literal">>=</code> </td><td>greater than or equal</td><td><code class="literal">ARRAY[1,4,3] >= ARRAY[1,4,3]</code></td><td><code class="literal">t</code></td></tr><tr><td> <code class="literal">@></code> </td><td>contains</td><td><code class="literal">ARRAY[1,4,3] @> ARRAY[3,1]</code></td><td><code class="literal">t</code></td></tr><tr><td> <code class="literal"><@</code> </td><td>is contained by</td><td><code class="literal">ARRAY[2,7] <@ ARRAY[1,7,4,2,6]</code></td><td><code class="literal">t</code></td></tr><tr><td> <code class="literal">&&</code> </td><td>overlap (have elements in common)</td><td><code class="literal">ARRAY[1,4,3] && ARRAY[2,1]</code></td><td><code class="literal">t</code></td></tr><tr><td> <code class="literal">||</code> </td><td>array-to-array concatenation</td><td><code class="literal">ARRAY[1,2,3] || ARRAY[4,5,6]</code></td><td><code class="literal">{1,2,3,4,5,6}</code></td></tr><tr><td> <code class="literal">||</code> </td><td>array-to-array concatenation</td><td><code class="literal">ARRAY[1,2,3] || ARRAY[[4,5,6],[7,8,9]]</code></td><td><code class="literal">{{1,2,3},{4,5,6},{7,8,9}}</code></td></tr><tr><td> <code class="literal">||</code> </td><td>element-to-array concatenation</td><td><code class="literal">3 || ARRAY[4,5,6]</code></td><td><code class="literal">{3,4,5,6}</code></td></tr><tr><td> <code class="literal">||</code> </td><td>array-to-element concatenation</td><td><code class="literal">ARRAY[4,5,6] || 7</code></td><td><code class="literal">{4,5,6,7}</code></td></tr></tbody></table></div></div><br class="table-break" /><p>
Array comparisons compare the array contents element-by-element,
using the default B-tree comparison function for the element data type.
In multidimensional arrays the elements are visited in row-major order
(last subscript varies most rapidly).
If the contents of two arrays are equal but the dimensionality is
different, the first difference in the dimensionality information
determines the sort order. (This is a change from versions of
<span class="productname">PostgreSQL</span> prior to 8.2: older versions would claim
that two arrays with the same contents were equal, even if the
number of dimensions or subscript ranges were different.)
</p><p>
See <a class="xref" href="arrays.html" title="8.15. Arrays">Section 8.15</a> for more details about array operator
behavior. See <a class="xref" href="indexes-types.html" title="11.2. Index Types">Section 11.2</a> for more details about
which operators support indexed operations.
</p><p>
<a class="xref" href="functions-array.html#ARRAY-FUNCTIONS-TABLE" title="Table 9.49. Array Functions">Table 9.49</a> shows the functions
available for use with array types. See <a class="xref" href="arrays.html" title="8.15. Arrays">Section 8.15</a>
for more information and examples of the use of these functions.
</p><a id="id-1.5.8.23.7" class="indexterm"></a><a id="id-1.5.8.23.8" class="indexterm"></a><a id="id-1.5.8.23.9" class="indexterm"></a><a id="id-1.5.8.23.10" class="indexterm"></a><a id="id-1.5.8.23.11" class="indexterm"></a><a id="id-1.5.8.23.12" class="indexterm"></a><a id="id-1.5.8.23.13" class="indexterm"></a><a id="id-1.5.8.23.14" class="indexterm"></a><a id="id-1.5.8.23.15" class="indexterm"></a><a id="id-1.5.8.23.16" class="indexterm"></a><a id="id-1.5.8.23.17" class="indexterm"></a><a id="id-1.5.8.23.18" class="indexterm"></a><a id="id-1.5.8.23.19" class="indexterm"></a><a id="id-1.5.8.23.20" class="indexterm"></a><a id="id-1.5.8.23.21" class="indexterm"></a><a id="id-1.5.8.23.22" class="indexterm"></a><a id="id-1.5.8.23.23" class="indexterm"></a><div class="table" id="ARRAY-FUNCTIONS-TABLE"><p class="title"><strong>Table 9.49. Array Functions</strong></p><div class="table-contents"><table class="table" summary="Array Functions" border="1"><colgroup><col /><col /><col /><col /><col /></colgroup><thead><tr><th>Function</th><th>Return Type</th><th>Description</th><th>Example</th><th>Result</th></tr></thead><tbody><tr><td>
<code class="literal">
<code class="function">array_append</code>(<code class="type">anyarray</code>, <code class="type">anyelement</code>)
</code>
</td><td><code class="type">anyarray</code></td><td>append an element to the end of an array</td><td><code class="literal">array_append(ARRAY[1,2], 3)</code></td><td><code class="literal">{1,2,3}</code></td></tr><tr><td>
<code class="literal">
<code class="function">array_cat</code>(<code class="type">anyarray</code>, <code class="type">anyarray</code>)
</code>
</td><td><code class="type">anyarray</code></td><td>concatenate two arrays</td><td><code class="literal">array_cat(ARRAY[1,2,3], ARRAY[4,5])</code></td><td><code class="literal">{1,2,3,4,5}</code></td></tr><tr><td>
<code class="literal">
<code class="function">array_ndims</code>(<code class="type">anyarray</code>)
</code>
</td><td><code class="type">int</code></td><td>returns the number of dimensions of the array</td><td><code class="literal">array_ndims(ARRAY[[1,2,3], [4,5,6]])</code></td><td><code class="literal">2</code></td></tr><tr><td>
<code class="literal">
<code class="function">array_dims</code>(<code class="type">anyarray</code>)
</code>
</td><td><code class="type">text</code></td><td>returns a text representation of array's dimensions</td><td><code class="literal">array_dims(ARRAY[[1,2,3], [4,5,6]])</code></td><td><code class="literal">[1:2][1:3]</code></td></tr><tr><td>
<code class="literal">
<code class="function">array_fill</code>(<code class="type">anyelement</code>, <code class="type">int[]</code>
[<span class="optional">, <code class="type">int[]</code></span>])
</code>
</td><td><code class="type">anyarray</code></td><td>returns an array initialized with supplied value and
dimensions, optionally with lower bounds other than 1</td><td><code class="literal">array_fill(7, ARRAY[3], ARRAY[2])</code></td><td><code class="literal">[2:4]={7,7,7}</code></td></tr><tr><td>
<code class="literal">
<code class="function">array_length</code>(<code class="type">anyarray</code>, <code class="type">int</code>)
</code>
</td><td><code class="type">int</code></td><td>returns the length of the requested array dimension</td><td><code class="literal">array_length(array[1,2,3], 1)</code></td><td><code class="literal">3</code></td></tr><tr><td>
<code class="literal">
<code class="function">array_lower</code>(<code class="type">anyarray</code>, <code class="type">int</code>)
</code>
</td><td><code class="type">int</code></td><td>returns lower bound of the requested array dimension</td><td><code class="literal">array_lower('[0:2]={1,2,3}'::int[], 1)</code></td><td><code class="literal">0</code></td></tr><tr><td>
<code class="literal">
<code class="function">array_position</code>(<code class="type">anyarray</code>, <code class="type">anyelement</code> [<span class="optional">, <code class="type">int</code></span>])
</code>
</td><td><code class="type">int</code></td><td>returns the subscript of the first occurrence of the second
argument in the array, starting at the element indicated by the third
argument or at the first element (array must be one-dimensional)</td><td><code class="literal">array_position(ARRAY['sun','mon','tue','wed','thu','fri','sat'], 'mon')</code></td><td><code class="literal">2</code></td></tr><tr><td>
<code class="literal">
<code class="function">array_positions</code>(<code class="type">anyarray</code>, <code class="type">anyelement</code>)
</code>
</td><td><code class="type">int[]</code></td><td>returns an array of subscripts of all occurrences of the second
argument in the array given as first argument (array must be
one-dimensional)</td><td><code class="literal">array_positions(ARRAY['A','A','B','A'], 'A')</code></td><td><code class="literal">{1,2,4}</code></td></tr><tr><td>
<code class="literal">
<code class="function">array_prepend</code>(<code class="type">anyelement</code>, <code class="type">anyarray</code>)
</code>
</td><td><code class="type">anyarray</code></td><td>append an element to the beginning of an array</td><td><code class="literal">array_prepend(1, ARRAY[2,3])</code></td><td><code class="literal">{1,2,3}</code></td></tr><tr><td>
<code class="literal">
<code class="function">array_remove</code>(<code class="type">anyarray</code>, <code class="type">anyelement</code>)
</code>
</td><td><code class="type">anyarray</code></td><td>remove all elements equal to the given value from the array
(array must be one-dimensional)</td><td><code class="literal">array_remove(ARRAY[1,2,3,2], 2)</code></td><td><code class="literal">{1,3}</code></td></tr><tr><td>
<code class="literal">
<code class="function">array_replace</code>(<code class="type">anyarray</code>, <code class="type">anyelement</code>, <code class="type">anyelement</code>)
</code>
</td><td><code class="type">anyarray</code></td><td>replace each array element equal to the given value with a new value</td><td><code class="literal">array_replace(ARRAY[1,2,5,4], 5, 3)</code></td><td><code class="literal">{1,2,3,4}</code></td></tr><tr><td>
<code class="literal">
<code class="function">array_to_string</code>(<code class="type">anyarray</code>, <code class="type">text</code> [<span class="optional">, <code class="type">text</code></span>])
</code>
</td><td><code class="type">text</code></td><td>concatenates array elements using supplied delimiter and
optional null string</td><td><code class="literal">array_to_string(ARRAY[1, 2, 3, NULL, 5], ',', '*')</code></td><td><code class="literal">1,2,3,*,5</code></td></tr><tr><td>
<code class="literal">
<code class="function">array_upper</code>(<code class="type">anyarray</code>, <code class="type">int</code>)
</code>
</td><td><code class="type">int</code></td><td>returns upper bound of the requested array dimension</td><td><code class="literal">array_upper(ARRAY[1,8,3,7], 1)</code></td><td><code class="literal">4</code></td></tr><tr><td>
<code class="literal">
<code class="function">cardinality</code>(<code class="type">anyarray</code>)
</code>
</td><td><code class="type">int</code></td><td>returns the total number of elements in the array, or 0 if the array is empty</td><td><code class="literal">cardinality(ARRAY[[1,2],[3,4]])</code></td><td><code class="literal">4</code></td></tr><tr><td>
<code class="literal">
<code class="function">string_to_array</code>(<code class="type">text</code>, <code class="type">text</code> [<span class="optional">, <code class="type">text</code></span>])
</code>
</td><td><code class="type">text[]</code></td><td>splits string into array elements using supplied delimiter and
optional null string</td><td><code class="literal">string_to_array('xx~^~yy~^~zz', '~^~', 'yy')</code></td><td><code class="literal">{xx,NULL,zz}</code></td></tr><tr><td>
<code class="literal">
<code class="function">unnest</code>(<code class="type">anyarray</code>)
</code>
</td><td><code class="type">setof anyelement</code></td><td>expand an array to a set of rows</td><td><code class="literal">unnest(ARRAY[1,2])</code></td><td><pre class="literallayout">1
2</pre>(2 rows)</td></tr><tr><td>
<code class="literal">
<code class="function">unnest</code>(<code class="type">anyarray</code>, <code class="type">anyarray</code> [, ...])
</code>
</td><td><code class="type">setof anyelement, anyelement [, ...]</code></td><td>expand multiple arrays (possibly of different types) to a set
of rows. This is only allowed in the FROM clause; see
<a class="xref" href="queries-table-expressions.html#QUERIES-TABLEFUNCTIONS" title="7.2.1.4. Table Functions">Section 7.2.1.4</a></td><td><code class="literal">unnest(ARRAY[1,2],ARRAY['foo','bar','baz'])</code></td><td><pre class="literallayout">1 foo
2 bar
NULL baz</pre>(3 rows)</td></tr></tbody></table></div></div><br class="table-break" /><p>
In <code class="function">array_position</code> and <code class="function">array_positions</code>,
each array element is compared to the searched value using
<code class="literal">IS NOT DISTINCT FROM</code> semantics.
</p><p>
In <code class="function">array_position</code>, <code class="literal">NULL</code> is returned
if the value is not found.
</p><p>
In <code class="function">array_positions</code>, <code class="literal">NULL</code> is returned
only if the array is <code class="literal">NULL</code>; if the value is not found in
the array, an empty array is returned instead.
</p><p>
In <code class="function">string_to_array</code>, if the delimiter parameter is
NULL, each character in the input string will become a separate element in
the resulting array. If the delimiter is an empty string, then the entire
input string is returned as a one-element array. Otherwise the input
string is split at each occurrence of the delimiter string.
</p><p>
In <code class="function">string_to_array</code>, if the null-string parameter
is omitted or NULL, none of the substrings of the input will be replaced
by NULL.
In <code class="function">array_to_string</code>, if the null-string parameter
is omitted or NULL, any null elements in the array are simply skipped
and not represented in the output string.
</p><div class="note"><h3 class="title">Note</h3><p>
There are two differences in the behavior of <code class="function">string_to_array</code>
from pre-9.1 versions of <span class="productname">PostgreSQL</span>.
First, it will return an empty (zero-element) array rather than NULL when
the input string is of zero length. Second, if the delimiter string is
NULL, the function splits the input into individual characters, rather
than returning NULL as before.
</p></div><p>
See also <a class="xref" href="functions-aggregate.html" title="9.20. Aggregate Functions">Section 9.20</a> about the aggregate
function <code class="function">array_agg</code> for use with arrays.
</p></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="functions-conditional.html">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="functions.html">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="functions-range.html">Next</a></td></tr><tr><td width="40%" align="left" valign="top">9.17. Conditional Expressions </td><td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td><td width="40%" align="right" valign="top"> 9.19. Range Functions and Operators</td></tr></table></div></body></html>
