<?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.10 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,3]</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,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> The array ordering operators (<code class="literal"><</code>, <code class="literal">>=</code>, etc) compare the array contents element-by-element, using the default B-tree comparison function for the element data type, and sort based on the first difference. 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> The array containment operators (<code class="literal"><@</code> and <code class="literal">@></code>) consider one array to be contained in another one if each of its elements appears in the other one. Duplicates are not treated specially, thus <code class="literal">ARRAY[1]</code> and <code class="literal">ARRAY[1,1]</code> are each considered to contain the other. </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.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><a id="id-1.5.8.23.24" 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 xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navfooter"><hr></hr><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="functions-conditional.html" title="9.17. Conditional Expressions">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="functions.html" title="Chapter 9. Functions and Operators">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="functions-range.html" title="9.19. Range Functions and Operators">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" title="PostgreSQL 11.10 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 9.19. Range Functions and Operators</td></tr></table></div></body></html>