DuckDB provides us with a bunch of list concatenation functions that do exactly the same thing; concatenate two lists. Actually, they’re all synonyms and so they can all be used interchangeably. There’s also a more general concatenation function that can also be used on lists.

So this article presents five functions that we can use to concatenate lists.

The Functions

The functions are:

• concat(): This function is mainly used for concatenating two or more strings, but it can also be used to concatenate two or more lists. NULL inputs are skipped.
• list_concat(): Concatenates two lists. NULL inputs are skipped.
• list_cat(): Alias for list_concat().
• array_concat(): Alias for list_concat().
• array_cat(): Alias for list_concat().

Example

Here’s an example that shows all five functions together in the same query:

.mode line
SET VARIABLE list_1 = ['Scratch', NULL, 'Purr'];
SET VARIABLE list_2 = ['Bark', 'Wag'];
SELECT 
    concat(getvariable('list_1'),getvariable('list_2')) AS concat,
    list_concat(getvariable('list_1'),getvariable('list_2')) AS list_concat,
    list_cat(getvariable('list_1'),getvariable('list_2')) AS list_cat,
    array_concat(getvariable('list_1'),getvariable('list_2')) AS array_concat,
    array_cat(getvariable('list_1'),getvariable('list_2')) AS array_cat;

Output:

      concat = [Scratch, NULL, Purr, Bark, Wag]
 list_concat = [Scratch, NULL, Purr, Bark, Wag]
    list_cat = [Scratch, NULL, Purr, Bark, Wag]
array_concat = [Scratch, NULL, Purr, Bark, Wag]
   array_cat = [Scratch, NULL, Purr, Bark, Wag]

All functions did the same thing; they concatenated the two lists.

Handling NULL LIsts

We can see that the functions kept any NULL values within the lists. However, if we try to concatenate a NULL list, then it simply skips the NULL list:

SET VARIABLE list_1 = ['Scratch', NULL, 'Purr'];
SELECT 
    concat(getvariable('list_1'),NULL) AS concat,
    list_concat(getvariable('list_1'),NULL) AS list_concat,
    list_cat(getvariable('list_1'),NULL) AS list_cat,
    array_concat(getvariable('list_1'),NULL) AS array_concat,
    array_cat(getvariable('list_1'),NULL) AS array_cat;

Output:

      concat = [Scratch, NULL, Purr]
 list_concat = [Scratch, NULL, Purr]
    list_cat = [Scratch, NULL, Purr]
array_concat = [Scratch, NULL, Purr]
   array_cat = [Scratch, NULL, Purr]

Concatenating Three or More Lists

Of the above functions, only one allows us to concatenate three or more lists in the one operation. That would be the concat() function. At least, that’s the case at the time of this writing.

Example:

SET VARIABLE list_1 = ['Scratch', NULL, 'Purr'];
SET VARIABLE list_2 = ['Bark', 'Wag'];
SET VARIABLE list_3 = ['Tweet', 'Flutter'];

SELECT concat(
    getvariable('list_1'),
    getvariable('list_2'),
    getvariable('list_3')
    );

Result:

[Scratch, NULL, Purr, Bark, Wag, Tweet, Flutter]

So no problems with concatenating three lists.

The other functions only allow us to concatenate two lists. For example, here’s what happens if we try to use list_concat() to concatenate three lists:

SET VARIABLE list_1 = ['Scratch', NULL, 'Purr'];
SET VARIABLE list_2 = ['Bark', 'Wag'];
SET VARIABLE list_3 = ['Tweet', 'Flutter'];

SELECT list_concat(
    getvariable('list_1'),
    getvariable('list_2'),
    getvariable('list_3')
    );

Output:

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

LINE 1: SELECT list_concat(
               ^