Materialize Documentation
s
Join the Community github/materialize

List types

New in v0.5.1.

Lists are ordered sequences of homogenously typed elements. Lists' elements can be other lists, known as “layered lists.”

Detail Info
Quick Syntax LIST[[1,2],[3]]
Size Variable
Catalog name Anonymous, but nameable

Syntax

LIST [ element , ] ( query )
Field Use
element An element of any data type to place in the list. Note that all elements must be of the same type.

List functions + operators

Polymorphism

List functions and operators are polymorphic, which applies the following constraints to their arguments:

Operators

Operator Description
listany || listany Concatenate the two lists.
listany || listelementany Append the element to the list.
listelementany || listany Prepend the element to the list.

Functions

Function Computes
list_agg(x: any) -> L
Aggregate values (including nulls) as a list. (docs)
list_append(l: listany, e: listelementany) -> L
Appends e to l.
list_cat(l1: listany, l2: listany) -> L
Concatenates l1 and l2.
list_n_layers(l: listany) -> int
Experimental––Returns the number of dimensions on l.
list_length(l: listany) -> int
Return the number of elements in l.
list_length_max(l: listany, d: int) -> int
Experimental––Return the greatest length among all lists on dimension d of l.
list_prepend(e: listelementany, l: listany) -> listany
Prepends e to l.
list_remove(l: listany, e: listelementany) -> listany
Experimental––Returns the list l without any elements equal to the given value e. Comparisons are done using IS NOT DISTINCT FROM semantics, so it is possible to remove NULLs.

Details

Type name

The name of a list type is the name of its element type followed by list, e.g. int list. This rule can be applied recursively, e.g. int list list for a list of int lists which is a two-layer list.

Construction

You can construct lists using the LIST expression:

SELECT LIST[1, 2, 3];
  list
---------
 {1,2,3}

You can nest LIST constructors to create layered lists:

SELECT LIST[LIST['a', 'b'], LIST['c']];
    list
-------------
 {{a,b},{c}}

You can also elide the LIST keyword from the interior list expressions:

SELECT LIST[['a', 'b'], ['c']];
    list
-------------
 {{a,b},{c}}

New in v0.9.13: Alternatively, you can construct a list from the results of a subquery. The subquery must return a single column. Note that, in this form of the LIST expression, parentheses are used rather than square brackets.

SELECT LIST(SELECT x FROM test0 WHERE x > 0 ORDER BY x DESC LIMIT 3);
    x
---------
 {4,3,2}

Layered lists can be “ragged”, i.e. the length of lists in each layer can differ from one another. This differs from array, which requires that each dimension of a multidimensional array only contain arrays of the same length.

Note that you can also construct lists using the available text cast.

Accessing lists

You can access elements of lists through:

Indexing elements

To access an individual element of list, you can “index” into it using brackets ([]) and 1-index element positions:

SELECT LIST[['a', 'b'], ['c']][1];
 ?column?
----------
 {a,b}

Indexing operations can be chained together to descend the list’s layers:

SELECT LIST[['a', 'b'], ['c']][1][2];
 ?column?
----------
 b

If the index is invalid (either less than 1 or greater than the maximum index), lists return NULL.

SELECT LIST[['a', 'b'], ['c']][1][5] AS exceed_index;
 exceed_index
--------------

Lists have types based on their layers (unlike arrays' dimension), and error if you attempt to index a non-list element (i.e. indexing past the list’s last layer):

SELECT LIST[['a', 'b'], ['c']][1][2][3];
ERROR:  cannot subscript type string

Slicing ranges

To access contiguous ranges of a list, you can slice it using [first index : last index], using 1-indexed positions:

SELECT LIST[1,2,3,4,5][2:4] AS two_to_four;
  slice
---------
 {2,3,4}

You can omit the first index to use the first value in the list, and omit the last index to use all elements remaining in the list.

SELECT LIST[1,2,3,4,5][:3] AS one_to_three;
 one_to_three
--------------
 {1,2,3}
SELECT LIST[1,2,3,4,5][3:] AS three_to_five;
 three_to_five
---------------
 {3,4,5}

If the first index exceeds the list’s maximum index, the operation returns an empty list:

SELECT LIST[1,2,3,4,5][10:] AS exceed_index;
 exceed_index
--------------
 {}

If the last index exceeds the list’s maximum index, the operation returns all remaining elements up to its final element.

SELECT LIST[1,2,3,4,5][2:10] AS two_to_end;
 two_to_end
------------
 {2,3,4,5}

Performing successive slices behaves more like a traditional programming language taking slices of an array, rather than PostgreSQL’s slicing, which descends into each layer.

SELECT LIST[1,2,3,4,5][2:][2:3] AS successive;
 successive
------------
 {3,4}

Output format

We represent lists textually using an opening curly brace ({), followed by the textual representation of each element separated by commas (,), terminated by a closing curly brace (}). For layered lists, this format is applied recursively to each list layer. No additional whitespace is added.

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

Elements whose textual representations contain curly braces, commas, whitespace, double quotes, backslashes, or which are exactly the string NULL (in any case) get wrapped in double quotes in order to distinguish the representation of the element from the representation of the containing list. 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 LIST[['a', 'white space'], [NULL, ''], ['escape"m\e', 'nUlL']];
                        list
-----------------------------------------------------
 {{a,"white space"},{NULL,""},{"escape\"m\\e",nUlL}}

text to list casts

New in v0.5.2.

To cast text to a list, you must format the text similar to list’s output format.

The text you cast must:

List vs. array

list is a Materialize-specific type and is designed to provide:

This section focuses on the distinctions between Materialize’s list and array types, but with some knowledge of the PostgreSQL array type, you should also be able to infer how list differs from it, as well.

Terminology

Feature Array term List term
Nested structure Multidimensional array Layered list
Accessing single element Subscripting Indexing1
Accessing range of elements Subscripting Slicing1

1In some places, such as error messages, Materialize refers to both list indexing and list slicing as subscripting.

Type definitions

Lists require explicitly declared layers, and each possible layer is treated as a distinct type. For example, a list of ints with two layers is int list list and one with three is int list list list. Because their number of lyaers differ, they cannot be used interchangeably.

Arrays only have one type for each non-array type, and all arrays share that type irrespective of their dimensions. This means that arrays of the same element type can be used interchangeably in most situations, without regard to their dimension. For example, arrays of text are all of type text[] and 1D, 2D, and 3D text[] can all be used in the same columns.

Nested structures

Lists allow each element of a layer to be of a different length. For example, in a two-layer list, each of the first layer’s lists can be of a different length:

SELECT LIST[[1,2], [3]] AS ragged_list;
ragged_list
-------------
{{1,2},{3}}

This is known as a “ragged list.”

Arrays require each element of a dimension to have the same length. For example, if the first element in a 2D list has a length of 2, all subsequent members must also have a length of 2.

SELECT ARRAY[[1,2], [3]] AS ragged_array;
ERROR:  number of array elements (3) does not match declared cardinality (4)

Accessing single elements

Lists support accessing single elements via indexing. When indexed, lists return a value with one less layer than the indexed list. For example, indexing a two-layer list returns a one-layer list.

SELECT LIST[['foo'],['bar']][1] AS indexing;
 indexing
--------------
 {foo}

Attempting to index twice into a text list (i.e. a one-layer list), fails because you cannot index text.

SELECT LIST['foo'][1][2];
ERROR:  cannot subscript type text
Accessing ranges of elements

Lists support accessing ranges of elements via slicing. However, lists do not currently support PostgreSQL-style slicing, which descends into layers in each slice.

Arrays require each element of a dimension to have the same length. For example, if the first element in a 2D list has a length of 2, all subsequent members must also have a length of 2.

Custom types

You can create custom list types, which lets you create a named entry in the catalog for a specific type of list.

Currently, custom types only provides a shorthand for referring to otherwise-annoying-to-type names, but in the future will provide binary encoding and decoding for these types, as well.

Note that custom list types have special rules regarding polymorphism.

Valid casts

Between lists

You can cast one list type to another if the type of the source list’s elements can be cast to the target list’s elements’ type. For example, float list can be cast to int list, but float list cannot be cast to timestamp list.

Note that this rule also applies to casting between custom list types.

From list

You can cast list to:

To list

You can cast the following types to list:

Known limitations

Examples

Literals

SELECT LIST[[1.5, NULL],[2.25]];
         list
----------------------
 {{1.50,NULL},{2.25}}

Casting between lists

SELECT LIST[[1.5, NULL],[2.25]]::int list list;
      list
----------------
 {{2,NULL},{2}}

Casting to text

SELECT LIST[[1.5, NULL],[2.25]]::text;
      list
------------------
 {{1,NULL},{2}}

Despite the fact that the output looks the same as the above examples, it is, in fact, text.

SELECT length(LIST[[1.5, NULL],[2.25]]::text);
 length
--------
     20

Casting from text

SELECT '{{1.5,NULL},{2.25}}'::numeric(38,2) list list AS text_to_list;
     text_to_list
----------------------
 {{1.50,NULL},{2.25}}