Contents

ARRAY[]

A constructor for arrays

ARRAY[] is a constructor for producing arrays in an expression.

ARRAY[] was added in PostgreSQL 7.4.

Usage

ARRAY[] can be used to generate a single, or if nested, a multi-dimensional array. The values provided must resolve to a common data type, or alternatively an explicit cast can be provided.

Change history

Examples

Simple usage example for ARRAY[], creating a one-dimensional array:

postgres=# SELECT ARRAY[1,2,3];
  array  
---------
 {1,2,3}
(1 row)

A multi-dimensional array can be created by nesting ARRAY[] invocations, here with two dimensions:

postgres=# SELECT ARRAY[ ARRAY[1,2,3], ARRAY[4,5,6] ];
       array       
-------------------
 {{1,2,3},{4,5,6}}
(1 row)

Each dimension of the array must contain the same number of elements:

postgres=# SELECT ARRAY[ ARRAY[1,2,3], ARRAY[4,5] ];
ERROR:  multidimensional arrays must have array expressions with matching dimensions

Each dimension of the array must be resolvable to a common data type:

postgres=# SELECT ARRAY[ ARRAY[1,2,3], ARRAY['a','b','c'] ];
ERROR:  ARRAY could not convert type text[] to integer[]
LINE 1: SELECT ARRAY[ ARRAY[1,2,3], ARRAY['a','b','c'] ];

Provided values will be resolved to a common data type, if this is unambiguously possible:

postgres=# SELECT ARRAY[1,'2',3];
  array  
---------
 {1,2,3}
(1 row)

postgres=# SELECT ARRAY['a',2,3];
ERROR:  invalid input syntax for type integer: "a"
LINE 1: SELECT ARRAY['a',2,3];

It is possible to provide an explicit cast to specify the array type:

postgres=# SELECT ARRAY['a',2,3]::text[];
  array  
---------
 {a,2,3}
(1 row)

Categories

Arrays