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
- PostgreSQL 7.4
- added (commit 730840c9)
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)
References
- PostgreSQL documentation: Array Constructors