Skip to main content

Edit this page

List

A LIST column encodes lists of values. Fields in the column can have values with different lengths, but they must all have the same underlying type. LISTs are typically used to store arrays of numbers, but can contain any uniform data type, including other LISTs and STRUCTs.

LISTs are similar to PostgreSQL's ARRAY type. SereneDB uses the LIST terminology, but some array_ functions are provided for PostgreSQL compatibility.

See the data types overview for a comparison between nested data types.

Creating Lists​

Lists can be created using the list_value(expr, ...) function or the equivalent bracket notation [expr, ...]. The expressions can be constants or arbitrary expressions. To create a list from a table column, use the list aggregate function.

List of integers:

Query
SELECT [1, 2, 3];
Result
 list_value------------ {1,2,3}

List of strings with a NULL value:

Query
SELECT ['alpha', 'beta', NULL, 'gamma'];
Result
 list_value------------------------- {alpha,beta,NULL,gamma}

List of lists with NULL values:

Query
SELECT [['alpha', 'beta', 'gamma'], NULL, ['delta', 'epsilon'], []];
Result
 list_value---------------------------------------------- {{alpha,beta,gamma},NULL,{delta,epsilon},{}}

Create a list with the list_value function:

Query
SELECT list_value(1, 2, 3);
Result
 list_value------------ {1,2,3}

Create a table with an INTEGER list column and a VARCHAR list column:

Query
CREATE TABLE list_table (int_list INTEGER[], varchar_list VARCHAR[]);

Retrieving from Lists​

Retrieving one or more values from a list can be accomplished using brackets and slicing notation, or through list functions like list_extract. Multiple equivalent functions are provided as aliases for compatibility with systems that refer to lists as arrays. For example, the function array_slice.

Query
SELECT $$['a', 'b', 'c'][3]$$                AS "Example", (['a', 'b', 'c'][3])::VARCHAR                AS "Result"UNION ALL SELECT $$['a', 'b', 'c'][-1]$$,                  (['a', 'b', 'c'][-1])::VARCHARUNION ALL SELECT $$['a', 'b', 'c'][2 + 1]$$,               (['a', 'b', 'c'][2 + 1])::VARCHARUNION ALL SELECT $$list_extract(['a', 'b', 'c'], 3)$$,     (list_extract(['a', 'b', 'c'], 3))::VARCHARUNION ALL SELECT $$['a', 'b', 'c'][1:2]$$,                 (['a', 'b', 'c'][1:2])::VARCHARUNION ALL SELECT $$['a', 'b', 'c'][:2]$$,                  (['a', 'b', 'c'][:2])::VARCHARUNION ALL SELECT $$['a', 'b', 'c'][-2:]$$,                 (['a', 'b', 'c'][-2:])::VARCHARUNION ALL SELECT $$list_slice(['a', 'b', 'c'], 2, 3)$$,    (list_slice(['a', 'b', 'c'], 2, 3))::VARCHAR;
Result
 Example                           | Result-----------------------------------+-------- ['a', 'b', 'c'][3]                | c ['a', 'b', 'c'][-1]               | c ['a', 'b', 'c'][2 + 1]            | c list_extract(['a', 'b', 'c'], 3)  | c ['a', 'b', 'c'][1:2]              | [a, b] ['a', 'b', 'c'][:2]               | [a, b] ['a', 'b', 'c'][-2:]              | [b, c] list_slice(['a', 'b', 'c'], 2, 3) | [b, c]

Comparison and Ordering​

The LIST type can be compared using all the comparison operators. These comparisons can be used in logical expressions such as WHERE and HAVING clauses, and return BOOLEAN values.

The LIST ordering is defined positionally using the following rules, where min_len = min(len(l1), len(l2)).

  • Equality. l1 and l2 are equal, if for each i in [1, min_len]: l1[i] = l2[i].
  • Less Than. For the first index i in [1, min_len] where l1[i] != l2[i]: If l1[i] < l2[i], l1 is less than l2.

NULL values are compared following PostgreSQL's semantics. Lower nesting levels are used for tie-breaking.

Here are some queries returning true for the comparison.

Query
SELECT [1, 2] < [1, 3] AS result;
Result
 result-------- t
Query
SELECT [[1], [2, 4, 5]] < [[2]] AS result;
Result
 result-------- t
Query
SELECT [ ] < [1] AS result;
Result
 result-------- t
Query
SELECT [1, 2] < [1, NULL, 4] AS result;
Result
 result-------- t

These queries return false.

Query
SELECT [ ] < [ ] AS result;
Result
 result-------- f
Query
SELECT [1, 2] < [1] AS result;
Result
 result-------- f

Functions​

See List Functions.