Appending Values to Lists with 3 DuckDB Functions

by

Appending values to lists is a common task when working with DuckDB and other DBMSs (not to mention programming languages in general). Usually we’ll get a function that’s purpose built for the task. As it turns out, DuckDB provides us with at least three functions to do this task.

The Functions

  • list_append()
  • array_append()
  • array_push_back()

The syntax goes like this:

list_append(list, element)
array_append(list, element)
array_push_back(list, element)

Example

Here’s a quick example that uses the list_append() function:

SELECT list_append(['banana', 'cherry'], 'apple');

Result:

[banana, cherry, apple]

Here, I added apple to the end of the list.

Here’s an example that shows all three functions together:

SELECT 
    list_append(['banana', 'cherry'], 'kiwi') AS list_append,
    array_append(['banana', 'cherry'], 'kiwi') AS array_append,
    array_push_back(['banana', 'cherry'], 'kiwi') AS array_push_back;

Result:

+------------------------+------------------------+------------------------+
|      list_append       |      array_append      |    array_push_back     |
+------------------------+------------------------+------------------------+
| [banana, cherry, kiwi] | [banana, cherry, kiwi] | [banana, cherry, kiwi] |
+------------------------+------------------------+------------------------+

Type Mismatch

When prepending elements to a list, the types must be compatible. Here’s what happens when they’re not:

SELECT list_append(['banana', 'cherry'], 7);

Result:

Binder Error: Cannot concatenate lists of types VARCHAR[] and INTEGER[] - an explicit cast is required
LINE 1: SELECT list_append(['banana', 'cherry'], 7);
                              ^

Appending NULL

It’s possible to append NULL to a list:

SELECT list_append(['banana', 'cherry'], NULL);

Result:

[banana, cherry, NULL]

Appending to a List of NULL Values

We can also append a value to a list of NULL values:

SELECT list_append([ NULL, NULL ], 7);

Result:

[NULL, NULL, 7]

Appending to a NULL List

If the list itself is a NULL argument, a list is still generated with the appended item being the only item in the list:

SELECT list_append(NULL, 7);

Result:

[7]

When the List Argument Isn’t a List

Trying to append to a non-list argument results in an error:

SELECT list_append(7, 8);

Result:

Binder Error: No function matches the given name and argument types 'list_concat(INTEGER_LITERAL, INTEGER[])'. You might need to add explicit type casts.
	Candidate functions:
	list_concat(ANY[], ANY[]) -> ANY[]

LINE 1: SELECT list_append(7, 8);
               ^