Array Data Types

New in v0.5.0.

Arrays are a multidimensional sequence of any non-array type.

Details

Type name

The name of an array type is the name of an element type followed by square brackets ([]) . For example, the type int[] specifies an integer array.

For compatibility with PostgreSQL, array types may optionally indicate additional dimensions, as in int[][][], or the sizes of dimensions, as in int[3][4]. However, as in PostgreSQL, these additional annotations are ignored. Arrays of the same element type are considered to be of the same type regardless of their dimensions. For example, the type int[3][4] is exactly equivalent to the type int[].

To reduce confusion, we recommend that you use the simpler form of the type name whenever possible.

Construction

Available only in unstable builds: The ARRAY expression syntax.

You can construct arrays using the special ARRAY expression:

SELECT ARRAY[1, 2, 3]

  array
---------
 {1,2,3}

You can nest ARRAY constructors to create multidimensional arrays:

SELECT ARRAY[ARRAY['a', 'b'], ARRAY['c', 'd']]

     array
---------------
 {{a,b},{c,d}}

Alternatively, you can construct an array from the results subquery. These subqueries must return a single column. Note that, in this form of the ARRAY expression, parentheses are used rather than square brackets.

SELECT ARRAY(SELECT x FROM test0 WHERE x > 0 ORDER BY x DESC LIMIT 3);

    x
---------
 {4,3,2}

Arrays cannot be “ragged.” The length of each array expression must equal the length of all other array constructors in the same dimension. For example, the following ragged array is rejected:

SELECT ARRAY[ARRAY[1, 2], ARRAY[3]]

ERROR:  number of array elements (3) does not match declared cardinality (4)

Textual format

The textual representation of an array consists of an opening curly brace ({), followed by the textual representation of each element separated by commas (,), followed by a closing curly brace (}). For multidimensional arrays, this format is applied recursively to each array dimension. No additional whitespace is added.

Null elements are rendered as the literal string NULL. Non-null elements are rendered as if that element had been cast to text.

An element whose textual representation contains curly braces, commas, whitespace, double quotes, backslashes, or is exactly the string NULL (in any case) is wrapped in double quotes in order to distinguish the representation of the element from the representation of the containing array. Within double quotes, backslashes and double quotes are backslash-escaped.

The following example demonstrates the output format and includes many of the aforementioned special cases.

SELECT ARRAY[ARRAY['a', 'white space'], ARRAY[NULL, ''], ARRAY['escape"m\e', 'nUlL']]

                         array
-------------------------------------------------------
 {{a,"white space"},{NULL,""},{"escape\"m\\e","nUlL"}}

Catalog names

Builtin types (e.g. integer) have a builtin array type that can be referred to by prefixing the type catalog name name with an underscore. For example, integer’s catalog name is pg_catalog.int4, so its array type’s catalog name is pg_catalog_int4.

Valid casts

You can cast all array types to:

• text (by assignment)
• list (explicit)

Available only in unstable builds: You can cast text to any array type. The input must conform to the textual format described above, with the additional restriction that you cannot yet use a cast to construct a multidimensional array.

Array to list casts

New in v0.20.0:

You can cast any type of array to a list of the same element type, as long as the array has only 0 or 1 dimensions, i.e. you can cast integer[] to integer list, as long as the array is empty or does not contain any arrays itself.

SELECT pg_typeof('{1,2,3}`::integer[]::integer list);

integer list

Examples

SELECT '{1,2,3}'::int[]

  int4
---------
 {1,2,3}

SELECT ARRAY[ARRAY[1, 2], ARRAY[NULL, 4]]::text

      array
------------------
 {{1,2},{NULL,4}}