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.
how to
Using LIST_INTERSECT() to Extract Overlapping Elements from Two Lists in DuckDB
The list_intersect() can be quite a handy function when working with lists in DuckDB. It accepts two lists as arguments, and it returns a list of elements that are common to both lists. It essentially performs a set intersection operation while preserving the list data type.
Duplicate values are ignored, so only one value is returned even if there are multiple occurrences in one or both lists.
5 Functions that Return the Year from a Date in DuckDB
When working with dates in DuckDB, some common tasks we might need to perform include extracting date parts from a date or timestamp value. For example we might want to extract the year from a date. Fortunately, DuckDB provides us with an abundance of options for doing that.
In this article, we’ll look at five different functions extract the year from a date in DuckDB.
How to Get a Reproducible Result Set When Using the SAMPLE Clause in DuckDB
When working with large datasets in DuckDB, the SAMPLE clause offers an efficient way to query a subset of your data. However, unless you specifically construct your query to get repeatable results, this sampling will return a different set of results each time the query is run.
But we can change that. We can write our query to return the same random result set every time we run it.
This article explores how to achieve consistent, reproducible result sets when using the SAMPLE clause in DuckDB.
3 Ways to Count the Number of Elements in a List in DuckDB
When working with lists in DuckDB, it’s often useful to know how many elements you’re working with. Fortunately DuckDB provides us with several functions that help us determine this. Below are three options for counting up the elements in a list when using DuckDB.
DuckDB JSON_EXISTS(): Check if a Path Exists in a JSON Document
Most DuckDB distributions are shipped with the json extension, which enables us to work with JSON documents. DuckDB provides a bunch of JSON functions, including JSON_EXISTS(). The JSON_EXISTS() function allows us to check whether a specific path exists within a JSON document.
This article explores how this function works, along with examples.
Fix “list dimensions must be equal” in DuckDB
If you’re getting an error in DuckDB that includes “list dimensions must be equal“, it appears that you’re performing a list operation that requires the lists to be the same dimension, but the lists are of different dimensions.
To fix this error, be sure to use lists of equal dimensions when performing the operation.
Add Minutes to a Date/Time Value in DuckDB
Date/time arithmetic, such as adding one or more minutes to a date/time value, is an operation we often need to perform when using SQL databases such as DuckDB. As with most RDBMSs, DuckDB makes this easy for us to achieve.
Here are two options for adding one or more minutes to a date, timestamp, or time value.
Using TRY_CAST() to Handle Errors When Converting Between Data Types in DuckDB
Encountering errors while converting between data types can be frustrating when working with SQL databases like DuckDB. But it usually means that something’s wrong. In most cases these errors occur because we’re trying to perform an impossible conversion, like from a number to a date or something.
But sometimes errors can get in the way, especially when we’re trying to convert a bunch of values. Sometimes it would be better for the system to return NULL for such failed conversions than to return an error and mess up the whole operation. Fortunately, we can do this.
3 Functions that Return the Maximum Value from a List in DuckDB
Sometimes when working with DuckDB, we need to find the maximum value from a given list. This can often be the case when working with lists that contain numeric data. Fortunately DuckDB provides a few options for us for doing this.