> For the complete documentation index, see [llms.txt](https://docs.postgresql.tw/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.postgresql.tw/the-sql-language/functions/functions-array.md).

# 9.19. Array Functions and Operators \#

[Table 9.56](#ARRAY-OPERATORS-TABLE) shows the specialized operators available for array types. In addition to those, the usual comparison operators shown in [Table 9.1](/the-sql-language/functions/functions-comparison.md#FUNCTIONS-COMPARISON-OP-TABLE) are available for arrays. The comparison operators 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.

**Table 9.56. Array Operators**

| <p>Operator</p><p>Description</p><p>Example(s)</p>                                                                                                                                                                                                                                                                                                                                                                                                     |   |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |   |                                                                                 |   |                                                                                   |
| ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | - | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | - | ------------------------------------------------------------------------------- | - | --------------------------------------------------------------------------------- |
| <p><code>anyarray</code> <code>@></code> <code>anyarray</code> → <code>boolean</code></p><p>Does the first array contain the second, that is, does each element appearing in the second array equal some element of the first array? (Duplicates are not treated specially, thus <code>ARRAY\[1]</code> and <code>ARRAY\[1,1]</code> are each considered to contain the other.)</p><p><code>ARRAY\[1,4,3] @> ARRAY\[3,1,3]</code> → <code>t</code></p> |   |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |   |                                                                                 |   |                                                                                   |
| <p><code>anyarray</code> <code><@</code> <code>anyarray</code> → <code>boolean</code></p><p>Is the first array contained by the second?</p><p><code>ARRAY\[2,2,7] <@ ARRAY\[1,7,4,2,6]</code> → <code>t</code></p>                                                                                                                                                                                                                                     |   |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |   |                                                                                 |   |                                                                                   |
| <p><code>anyarray</code> <code>&&</code> <code>anyarray</code> → <code>boolean</code></p><p>Do the arrays overlap, that is, have any elements in common?</p><p><code>ARRAY\[1,4,3] && ARRAY\[2,1]</code> → <code>t</code></p>                                                                                                                                                                                                                          |   |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |   |                                                                                 |   |                                                                                   |
| <p><code>anycompatiblearray</code> <code>                                                                                                                                                                                                                                                                                                                                                                                                              |   | </code> <code>anycompatiblearray</code> → <code>anycompatiblearray</code></p><p>Concatenates the two arrays. Concatenating a null or empty array is a no-op; otherwise the arrays must have the same number of dimensions (as illustrated by the first example) or differ in number of dimensions by one (as illustrated by the second). If the arrays are not of identical element types, they will be coerced to a common type (see <a href="/pages/FJPc2e5dpI3xJRLr2xF2">Section 10.5</a>).</p><p><code>ARRAY\[1,2,3] |   | ARRAY\[4,5,6,7]</code> → <code>{1,2,3,4,5,6,7}</code></p><p><code>ARRAY\[1,2,3] |   | ARRAY\[\[4,5,6],\[7,8,9.9]]</code> → <code>{{1,2,3},{4,5,6},{7,8,9.9}}</code></p> |
| <p><code>anycompatible</code> <code>                                                                                                                                                                                                                                                                                                                                                                                                                   |   | </code> <code>anycompatiblearray</code> → <code>anycompatiblearray</code></p><p>Concatenates an element onto the front of an array (which must be empty or one-dimensional).</p><p><code>3                                                                                                                                                                                                                                                                                                                               |   | ARRAY\[4,5,6]</code> → <code>{3,4,5,6}</code></p>                               |   |                                                                                   |
| <p><code>anycompatiblearray</code> <code>                                                                                                                                                                                                                                                                                                                                                                                                              |   | </code> <code>anycompatible</code> → <code>anycompatiblearray</code></p><p>Concatenates an element onto the end of an array (which must be empty or one-dimensional).</p><p><code>ARRAY\[4,5,6]                                                                                                                                                                                                                                                                                                                          |   | 7</code> → <code>{4,5,6,7}</code></p>                                           |   |                                                                                   |

<br>

See [Section 8.15](/the-sql-language/datatype/arrays.md) for more details about array operator behavior. See [Section 11.2](/the-sql-language/indexes/indexes-types.md) for more details about which operators support indexed operations.

[Table 9.57](#ARRAY-FUNCTIONS-TABLE) shows the functions available for use with array types. See [Section 8.15](/the-sql-language/datatype/arrays.md) for more information and examples of the use of these functions.

**Table 9.57. Array Functions**

<table data-header-hidden><thead><tr><th></th></tr></thead><tbody><tr><td><p>Function</p><p>Description</p><p>Example(s)</p></td></tr><tr><td><p><code>array_append</code> ( <code>anycompatiblearray</code>, <code>anycompatible</code> ) → <code>anycompatiblearray</code></p><p>Appends an element to the end of an array (same as the <code>anycompatiblearray</code> <code>||</code> <code>anycompatible</code> operator).</p><p><code>array_append(ARRAY[1,2], 3)</code> → <code>{1,2,3}</code></p></td></tr><tr><td><p><code>array_cat</code> ( <code>anycompatiblearray</code>, <code>anycompatiblearray</code> ) → <code>anycompatiblearray</code></p><p>Concatenates two arrays (same as the <code>anycompatiblearray</code> <code>||</code> <code>anycompatiblearray</code> operator).</p><p><code>array_cat(ARRAY[1,2,3], ARRAY[4,5])</code> → <code>{1,2,3,4,5}</code></p></td></tr><tr><td><p><code>array_dims</code> ( <code>anyarray</code> ) → <code>text</code></p><p>Returns a text representation of the array's dimensions.</p><p><code>array_dims(ARRAY[[1,2,3], [4,5,6]])</code> → <code>[1:2][1:3]</code></p></td></tr><tr><td><p><code>array_fill</code> ( <code>anyelement</code>, <code>integer[]</code> [, <code>integer[]</code> ] ) → <code>anyarray</code></p><p>Returns an array filled with copies of the given value, having dimensions of the lengths specified by the second argument. The optional third argument supplies lower-bound values for each dimension (which default to all <code>1</code>).</p><p><code>array_fill(11, ARRAY[2,3])</code> → <code>{{11,11,11},{11,11,11}}</code></p><p><code>array_fill(7, ARRAY[3], ARRAY[2])</code> → <code>[2:4]={7,7,7}</code></p></td></tr><tr><td><p><code>array_length</code> ( <code>anyarray</code>, <code>integer</code> ) → <code>integer</code></p><p>Returns the length of the requested array dimension. (Produces NULL instead of 0 for empty or missing array dimensions.)</p><p><code>array_length(array[1,2,3], 1)</code> → <code>3</code></p><p><code>array_length(array[]::int[], 1)</code> → <code>NULL</code></p><p><code>array_length(array['text'], 2)</code> → <code>NULL</code></p></td></tr><tr><td><p><code>array_lower</code> ( <code>anyarray</code>, <code>integer</code> ) → <code>integer</code></p><p>Returns the lower bound of the requested array dimension.</p><p><code>array_lower('[0:2]={1,2,3}'::integer[], 1)</code> → <code>0</code></p></td></tr><tr><td><p><code>array_ndims</code> ( <code>anyarray</code> ) → <code>integer</code></p><p>Returns the number of dimensions of the array.</p><p><code>array_ndims(ARRAY[[1,2,3], [4,5,6]])</code> → <code>2</code></p></td></tr><tr><td><p><code>array_position</code> ( <code>anycompatiblearray</code>, <code>anycompatible</code> [, <code>integer</code> ] ) → <code>integer</code></p><p>Returns the subscript of the first occurrence of the second argument in the array, or <code>NULL</code> if it's not present. If the third argument is given, the search begins at that subscript. The array must be one-dimensional. Comparisons are done using <code>IS NOT DISTINCT FROM</code> semantics, so it is possible to search for <code>NULL</code>.</p><p><code>array_position(ARRAY['sun', 'mon', 'tue', 'wed', 'thu', 'fri', 'sat'], 'mon')</code> → <code>2</code></p></td></tr><tr><td><p><code>array_positions</code> ( <code>anycompatiblearray</code>, <code>anycompatible</code> ) → <code>integer[]</code></p><p>Returns an array of the subscripts of all occurrences of the second argument in the array given as first argument. The array must be one-dimensional. Comparisons are done using <code>IS NOT DISTINCT FROM</code> semantics, so it is possible to search for <code>NULL</code>. <code>NULL</code> is returned only if the array is <code>NULL</code>; if the value is not found in the array, an empty array is returned.</p><p><code>array_positions(ARRAY['A','A','B','A'], 'A')</code> → <code>{1,2,4}</code></p></td></tr><tr><td><p><code>array_prepend</code> ( <code>anycompatible</code>, <code>anycompatiblearray</code> ) → <code>anycompatiblearray</code></p><p>Prepends an element to the beginning of an array (same as the <code>anycompatible</code> <code>||</code> <code>anycompatiblearray</code> operator).</p><p><code>array_prepend(1, ARRAY[2,3])</code> → <code>{1,2,3}</code></p></td></tr><tr><td><p><code>array_remove</code> ( <code>anycompatiblearray</code>, <code>anycompatible</code> ) → <code>anycompatiblearray</code></p><p>Removes all elements equal to the given value from the array. The array must be one-dimensional. Comparisons are done using <code>IS NOT DISTINCT FROM</code> semantics, so it is possible to remove <code>NULL</code>s.</p><p><code>array_remove(ARRAY[1,2,3,2], 2)</code> → <code>{1,3}</code></p></td></tr><tr><td><p><code>array_replace</code> ( <code>anycompatiblearray</code>, <code>anycompatible</code>, <code>anycompatible</code> ) → <code>anycompatiblearray</code></p><p>Replaces each array element equal to the second argument with the third argument.</p><p><code>array_replace(ARRAY[1,2,5,4], 5, 3)</code> → <code>{1,2,3,4}</code></p></td></tr><tr><td><p><code>array_reverse</code> ( <code>anyarray</code> ) → <code>anyarray</code></p><p>Reverses the first dimension of the array.</p><p><code>array_reverse(ARRAY[[1,2],[3,4],[5,6]])</code> → <code>{{5,6},{3,4},{1,2}}</code></p></td></tr><tr><td><p><code>array_sample</code> ( <em><code>array</code></em> <code>anyarray</code>, <em><code>n</code></em> <code>integer</code> ) → <code>anyarray</code></p><p>Returns an array of <em><code>n</code></em> items randomly selected from <em><code>array</code></em>. <em><code>n</code></em> may not exceed the length of <em><code>array</code></em>'s first dimension. If <em><code>array</code></em> is multi-dimensional, an “item” is a slice having a given first subscript.</p><p><code>array_sample(ARRAY[1,2,3,4,5,6], 3)</code> → <code>{2,6,1}</code></p><p><code>array_sample(ARRAY[[1,2],[3,4],[5,6]], 2)</code> → <code>{{5,6},{1,2}}</code></p></td></tr><tr><td><p><code>array_shuffle</code> ( <code>anyarray</code> ) → <code>anyarray</code></p><p>Randomly shuffles the first dimension of the array.</p><p><code>array_shuffle(ARRAY[[1,2],[3,4],[5,6]])</code> → <code>{{5,6},{1,2},{3,4}}</code></p></td></tr><tr><td><p><code>array_sort</code> ( <em><code>array</code></em> <code>anyarray</code> [, <em><code>descending</code></em> <code>boolean</code> [, <em><code>nulls_first</code></em> <code>boolean</code> ]] ) → <code>anyarray</code></p><p>Sorts the first dimension of the array. The sort order is determined by the default sort ordering of the array's element type; however, if the element type is collatable, the collation to use can be specified by adding a <code>COLLATE</code> clause to the <em><code>array</code></em> argument.</p><p>If <em><code>descending</code></em> is true then sort in descending order, otherwise ascending order. If omitted, the default is ascending order. If <em><code>nulls_first</code></em> is true then nulls appear before non-null values, otherwise nulls appear after non-null values. If omitted, <em><code>nulls_first</code></em> is taken to have the same value as <em><code>descending</code></em>.</p><p><code>array_sort(ARRAY[[2,4],[2,1],[6,5]])</code> → <code>{{2,1},{2,4},{6,5}}</code></p></td></tr><tr><td><p><code>array_to_string</code> ( <em><code>array</code></em> <code>anyarray</code>, <em><code>delimiter</code></em> <code>text</code> [, <em><code>null_string</code></em> <code>text</code> ] ) → <code>text</code></p><p>Converts each array element to its text representation, and concatenates those separated by the <em><code>delimiter</code></em> string. If <em><code>null_string</code></em> is given and is not <code>NULL</code>, then <code>NULL</code> array entries are represented by that string; otherwise, they are omitted. See also <a href="/pages/iwmfUPDBij12p3VynvZm#FUNCTION-STRING-TO-ARRAY"><code>string_to_array</code></a>.</p><p><code>array_to_string(ARRAY[1, 2, 3, NULL, 5], ',', '*')</code> → <code>1,2,3,*,5</code></p></td></tr><tr><td><p><code>array_upper</code> ( <code>anyarray</code>, <code>integer</code> ) → <code>integer</code></p><p>Returns the upper bound of the requested array dimension.</p><p><code>array_upper(ARRAY[1,8,3,7], 1)</code> → <code>4</code></p></td></tr><tr><td><p><code>cardinality</code> ( <code>anyarray</code> ) → <code>integer</code></p><p>Returns the total number of elements in the array, or 0 if the array is empty.</p><p><code>cardinality(ARRAY[[1,2],[3,4]])</code> → <code>4</code></p></td></tr><tr><td><p><code>trim_array</code> ( <em><code>array</code></em> <code>anyarray</code>, <em><code>n</code></em> <code>integer</code> ) → <code>anyarray</code></p><p>Trims an array by removing the last <em><code>n</code></em> elements. If the array is multidimensional, only the first dimension is trimmed.</p><p><code>trim_array(ARRAY[1,2,3,4,5,6], 2)</code> → <code>{1,2,3,4}</code></p></td></tr><tr><td><p><code>unnest</code> ( <code>anyarray</code> ) → <code>setof anyelement</code></p><p>Expands an array into a set of rows. The array's elements are read out in storage order.</p><p><code>unnest(ARRAY[1,2])</code> →</p><pre><code> 1
 2
</code></pre><p><code>unnest(ARRAY[['foo','bar'],['baz','quux']])</code> →</p><pre><code> foo
 bar
 baz
 quux
</code></pre></td></tr><tr><td><p><code>unnest</code> ( <code>anyarray</code>, <code>anyarray</code> [, ... ] ) → <code>setof anyelement, anyelement [, ... ]</code></p><p>Expands multiple arrays (possibly of different data types) into a set of rows. If the arrays are not all the same length then the shorter ones are padded with <code>NULL</code>s. This form is only allowed in a query's FROM clause; see <a href="/pages/95EI0PwPr8hP7MU5hg1k#QUERIES-TABLEFUNCTIONS">Section 7.2.1.4</a>.</p><p><code>select * from unnest(ARRAY[1,2], ARRAY['foo','bar','baz']) as x(a,b)</code> →</p><pre><code> a |  b
---+-----
 1 | foo
 2 | bar
   | baz
</code></pre></td></tr></tbody></table>

<br>

See also [Section 9.21](/the-sql-language/functions/functions-aggregate.md) about the aggregate function `array_agg` for use with arrays.

***

原文：[PostgreSQL 18.6 Documentation](https://www.postgresql.org/docs/18/functions-array.html)（英文原文，待翻譯）


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:

```
GET https://docs.postgresql.tw/the-sql-language/functions/functions-array.md?ask=<question>&goal=<endgoal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.