DuckDB provides us with a few functions that prepend a value to a list. Actually, these functions are synonymous, so they all do the same thing, with the same/similar basic syntax.

The Functions

• list_prepend()
• array_prepend()
• array_push_front()

The syntax for the first two goes like this:

list_prepend(element, list)
array_prepend(element, list)

Whereas array_push_front() goes like this:

array_push_front(list, element)

In all cases, element is the value we want to prepend to list.

So, while the DuckDB documentation states that array_prepend() and array_push_front() are aliases of list_prepend(), it’s interesting to note that the syntax for array_push_front() is different to the others. With array_push_front(), the first argument is the list, and the second argument is the element to prepend. With the other functions, it’s the other way around.

Example

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

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

Result:

[apple, banana, cherry] 

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

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

SELECT 
    list_prepend('kiwi', ['banana', 'cherry']) AS list_prepend,
    array_prepend('kiwi', ['banana', 'cherry']) AS array_prepend,
    array_push_front(['banana', 'cherry'], 'kiwi') AS array_push_front;

Result:

+------------------------+------------------------+------------------------+
|      list_prepend      |     array_prepend      |    array_push_front    |
+------------------------+------------------------+------------------------+
| [kiwi, banana, cherry] | [kiwi, banana, cherry] | [kiwi, banana, cherry] |
+------------------------+------------------------+------------------------+

We can see that array_push_front() requires its argument in a different order to the other two functions. Here’s what happens if we use the same order as the other two:

SELECT array_push_front('kiwi', ['banana', 'cherry']);

Result:

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

LINE 1: SELECT array_push_front('kiwi', ['banana', 'ch...
               ^

Type Mismatch

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

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

Result:

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

Prepending NULL

It’s possible to prepend NULL to a list:

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

Result:

[NULL, banana, cherry] 

Prepending to a List of NULL Values

It’s also possible to prepend a value to a list of NULL values:

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

Result:

[7, NULL, NULL]

Prepending to a NULL List

If the list itself is a NULL argument, a list is still generated, and the prepended item ends up as the only item in the list:

SELECT list_prepend(7, NULL);

Result:

[7]

When the List Argument Isn’t a List

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

SELECT list_prepend(7, 8);

Result:

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

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