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.”

Syntax

List functions + operators

Polymorphism

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

• All instances of listany must be lists of the same type.
• All instances of listelementany must be of the same type.
• If a function uses both listany and listelementany parameters, all instances of listany must be a list of the type used in listelementany.
• If any value passed to a polymorphic parameter is a custom type, additional constraints apply.

Operators

Functions

list_agg(x: any) -> L

list_append(l: listany, e: listelementany) -> L

list_cat(l1: listany, l2: listany) -> L

list_n_layers(l: listany) -> int

list_length(l: listany) -> int

list_length_max(l: listany, d: int) -> int

list_prepend(e: listelementany, l: listany) -> listany

list_remove(l: listany, e: listelementany) -> listany

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 for individual elements
• Slicing for ranges of elements

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:

• Begin with an opening curly brace ({) and end with a closing curly brace (})

• Separate each element with a comma (,)

• Use a representation for elements that can be cast from text to the list’s element type.

  For example, to cast text to a date list, you use date’s text representation:

  SELECT '{2001-02-03, 2004-05-06}'::date list as date_list;
  

          date_list
  -------------------------
   {2001-02-03,2004-05-06}
  

  You cannot include the DATE keyword.

• Escape any special representations ({, }, ", \, whitespace, or the literal string NULL) you want parsed as text using…

  • Double quotes (") to escape an entire contiguous string

  • Backslashes (\) to escape an individual character in any context, e.g. \" to escape double quotes within an escaped string.

    Note that escaping any character in the string “null” (case-insensitive) generates a text value equal to “NULL” and not a NULL value.

  For example:

  SELECT '{
      "{brackets}",
      "\"quotes\"",
      \\slashes\\,
      \ leading space,
      trailing space\ ,
      \NULL
  }'::text list as escape_examples;
  

                                     escape_examples
  -------------------------------------------------------------------------------------
   {"{brackets}","\"quotes\"","\\slashes\\"," leading space","trailing space ","NULL"}
  

  Note that all unescaped whitespace is trimmed.

List vs. array

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

• Similar semantics to Avro and JSON arrays
• A more ergonomic experience than PostgreSQL-style arrays

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

¹In 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:

• text (implicitly)
• Other lists as noted above.

To list

You can cast the following types to list:

• arrays (explicitly). See details.
• text (explicitly). See details.
• Other lists as noted above.

Known limitations

• list data can only be sent to PostgreSQL as text (#4628)

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}}