Array Data Types
New in v0.5.0.
Arrays are a multidimensional sequence of any non-array type.
We do not recommend using arrays, which exist in Materialize primarily to facilitate compatibility with PostgreSQL. Specifically, many of the PostgreSQL compatibility views in the system catalog must expose array types. Unfortunately, PostgreSQL arrays have odd semantics and do not interoperate well with modern data formats like JSON and Avro.
Use the list
type instead.
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
ARRAY
expression syntax
is under construction and requires
experimental mode.
Available since v0.5.0.
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}}
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)
Output 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
.
Array element | Catalog name | OID |
---|---|---|
bigint |
pg_catalog._int8 |
1016 |
boolean |
pg_catalog._bool |
1000 |
date |
pg_catalog._date |
1182 |
double precision |
pg_catalog._float8 |
1022 |
integer |
pg_catalog._bool |
1007 |
interval |
pg_catalog._interval |
1187 |
jsonb |
pg_catalog.3807 |
1000 |
numeric |
pg_catalog._numeric |
1231 |
oid |
pg_catalog._oid |
1028 |
real |
pg_catalog._float4 |
1021 |
text |
pg_catalog._bool |
1009 |
time |
pg_catalog._time |
1183 |
timestamp |
pg_catalog._timestamp |
1115 |
timestamp with time zone |
pg_catalog._timestamptz |
1185 |
uuid |
pg_catalog._uuid |
2951 |
Valid casts
You can cast all array types to
text
by assignment.
You cannot presently cast any other type to an array type.
Examples
SELECT ARRAY[ARRAY[1, 2], ARRAY[NULL, 4]]::text
array
------------------
{{1,2},{NULL,4}}