2 Functions that Create a List in DuckDB

DuckDB provides us with plenty of functions for working with lists, including a couple that actually create lists for us.

In particular, the list_value() and list_pack() functions are specifically for creating lists in DuckDB. We’ll run through some examples of these functions below.

Basic Examples

Here are some basic examples that show each function in action.

The list_value() Function

Let’s start with the list_value() function:

SELECT list_value( 1, 2, 3 );

Result:

[1, 2, 3]

We simply pass each value that we want to be in the list, and the function returns a list with those elements.

The list_pack() Function

As it turns out, list_pack() is an alias of list_value(), so this means we can use either function to achieve the same result:

SELECT list_pack( 1, 2, 3 );

Result:

[1, 2, 3]

As with list_value(), we simply pass the values we want the list to contain. list_pack() then creates our list for us.

String Values

Here’s an example of creating a list of strings with each function:

SELECT 
    list_value( 'Coffee', 'Tea', 'Macha' ) AS list_value,
    list_pack( 'Coffee', 'Tea', 'Macha' ) AS list_pack;

Result:

+----------------------+----------------------+
|      list_value      |      list_pack       |
+----------------------+----------------------+
| [Coffee, Tea, Macha] | [Coffee, Tea, Macha] |
+----------------------+----------------------+

Dates

We can do dates too:

SELECT 
    list_value( date '2020-10-01', date '2025-01-03', date '2035-08-10' ) AS list_value,
    list_pack( date '2020-10-01', date '2025-01-03', date '2035-08-10' ) AS list_pack;

Result:

+--------------------------------------+--------------------------------------+
|              list_value              |              list_pack               |
+--------------------------------------+--------------------------------------+
| [2020-10-01, 2025-01-03, 2035-08-10] | [2020-10-01, 2025-01-03, 2035-08-10] |
+--------------------------------------+--------------------------------------+

NULL Values

We can include NULL values in the list:

SELECT 
    list_value( 'Coffee', 'Tea', 'Macha', NULL ) AS list_value,
    list_pack( 'Coffee', 'Tea', 'Macha', NULL ) AS list_pack;

Result:

+----------------------------+----------------------------+
|         list_value         |         list_pack          |
+----------------------------+----------------------------+
| [Coffee, Tea, Macha, NULL] | [Coffee, Tea, Macha, NULL] |
+----------------------------+----------------------------+

Nested Lists

We can create lists that contain other lists. We have a couple of options for doing this. Here’s one option:

SELECT 
    list_value( ['Coffee', 'Tea'], ['Macha', NULL] ) AS list_value,
    list_pack( ['Coffee', 'Tea'], ['Macha', NULL] ) AS list_pack;

Result:

+--------------------------------+--------------------------------+
|           list_value           |           list_pack            |
+--------------------------------+--------------------------------+
| [[Coffee, Tea], [Macha, NULL]] | [[Coffee, Tea], [Macha, NULL]] |
+--------------------------------+--------------------------------+

Another option is to nest the functions inside themselves:

SELECT 
    list_value( 
        list_value('Coffee', 'Tea'), 
        list_value('Macha', NULL) 
        ) AS list_value,
    list_pack( 
        list_pack('Coffee', 'Tea'), 
        list_pack('Macha', NULL) 
        ) AS list_pack;

Result:

+--------------------------------+--------------------------------+
|           list_value           |           list_pack            |
+--------------------------------+--------------------------------+
| [[Coffee, Tea], [Macha, NULL]] | [[Coffee, Tea], [Macha, NULL]] |
+--------------------------------+--------------------------------+

Given the functions do the same thing, it’s also possible to mix and match. For example, nest list_pack() inside a list_value() and vice-versa:

SELECT 
    list_value( 
        list_pack('Coffee', 'Tea'), 
        list_pack('Macha', NULL) 
        ) AS list_value,
    list_pack( 
        list_value('Coffee', 'Tea'), 
        list_value('Macha', NULL) 
        ) AS list_pack;

Result:

+--------------------------------+--------------------------------+
|           list_value           |           list_pack            |
+--------------------------------+--------------------------------+
| [[Coffee, Tea], [Macha, NULL]] | [[Coffee, Tea], [Macha, NULL]] |
+--------------------------------+--------------------------------+