DuckDB has a function called list_distinct() that enables us to remove duplicate values from a list. Any value that appears more than once is “deduped” and is returned once in the resulting list.
The function also removes any NULL values.
Continue readingWhen working with DuckDB, we may need to determine whether a specific value exists within a list or array. Fortunately, DuckDB offers four synonymous functions that enable us to accomplish this.
Continue readingDuckDB has an unnest() function that we can use to unnest lists and structs. Well, it can also be applied to NULL, but that’ll return an empty result.
By “unnest” I mean it takes the list or struct, and it returns its contents as rows in a table. You might say that it converts lists and structs into tables, where each item in the list or struct becomes a row in the table.
Below are examples of using DuckDB’s unnest() function to unnest lists and structs.
If you’re getting an error that reads “Binder Error: ‘list_has_all’ cannot compare lists of different types” in DuckDB, it appears that you’re passing two different types of lists to the list_has_all() function.
DuckDB’s list_has_all() function accepts two lists as arguments. These need to be of the same type.
To fix this issue, make sure that both lists are of the same type.
Continue readingWhen working with lists in DuckDB, we sometimes need to check whether a list contains specific elements. The list_has_all() function is a handy tool that allows us to verify if all elements of one list exist within another. This function is particularly useful in filtering queries, data validation, and advanced list-based operations.
In this article, we’ll explore how list_has_all() works in DuckDB.
DuckDB has a flatten() function that we can use to flatten nested lists. The function concatenates a list of lists into a single list. So whether the outer list contains just one list or multiple lists, we can use the flatten() function to flatten them into one list.
However, it only goes one level deep, so that’s something to keep in mind.
Continue readingDuckDB 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.
Continue readingIn this article we look at how to automatically enclose values in single quotes when outputting DuckDB query results as a list. Using this method, each value is formatted as SQL literals. This can be useful when you want to escape the values for SQL or for some other purpose.
We also look at how CSV output is sometimes quoted with double quotes, and how we can adjust the results by doing things like removing the headers and changing the list separator.
Continue readingThe generate_subscripts() function in DuckDB creates a series of index values for accessing elements in an array. It can help us iterate through array indices when we need to work with array elements.
Here’s a quick look at DuckDB’s generate_subscripts() function, along with examples.
DuckDB’s command line interface (CLI) provides a convenient .mode csv command that allows you to output all query results directly in CSV format. This approach differs from the COPY statement as it affects the output format of all subsequent queries until changed.
The article takes a quick look at CSV mode in the DuckDB CLI.
Continue reading