DuckDB provides us with a good selection of list functions. This includes a list_any_value() function, which returns the first non-NULL value from a given list.
what is
The Difference Between RANGE() and GENERATE_SERIES() in DuckDB
DuckDB offers two handy functions for generating sequences of numbers: range() and generate_series(). While they both do the same thing, and share basically the same syntax, there is one important difference between them.
The primary difference between them is in their inclusivity behavior with regard to the stop value.
Convert a Table to a JSON Document with JSON_GROUP_OBJECT() in DuckDB
In DuckDB, the json_group_object() function is a convenient way to aggregate data into JSON objects by pairing keys and values across rows within groups.
The function is especially useful when we’re transforming tabular data into a more hierarchical or nested JSON structure for web APIs, reporting, or downstream processing. It helps pivot rows into a single JSON object, making the data more compact and easier to consume in applications that require JSON formats.
3 Functions for Extracting a Value from a List in DuckDB
DuckDB provides us with a list_extract() function that extracts a value from a list, based on the element’s index in that list.
But as if one function isn’t enough, it also provides us with a couple of aliases that we can use instead of list_extract() if we prefer.
So let’s call it three functions that we can use to extract a value from a list.
DUCKDB_TABLES() Examples
The duckdb_tables() function is a system function in DuckDB that provides useful metadata about all tables in your database. The function returns a table containing information about each table, including its schema, name, and various properties. It can be quite a handy tool for database introspection and management.
6 Functions for Working with the Unix Epoch in DuckDB
DuckDB offers a versatile set of functions to handle timestamps at various levels of precision. This article explores some of DuckDB’s functions that help us to convert between epoch representations and timestamps.
These specialized time conversion functions can be handy tools when working with temporal data, allowing seamless translation between human-readable timestamps and machine-optimized epoch representations at varying levels of precision.
The Difference Between DATE_DIFF() and DATE_SUB() in DuckDB
In DuckDB, the date_diff() (along with its synonym datediff()) and date_sub() (along with its synonym datesub()) functions allow us to get the difference between two dates. While they might seem similar, they actually calculate date differences in distinct ways that are important to understand for accurate data analysis.
Let’s take a look at the difference between these functions.
2 Functions that Create a List in DuckDB
DuckDB provides us with plenty of functions for working with lists, including a couple that actually create lists for us.
In particular, the list_value() and list_pack() functions are specifically for creating lists in DuckDB. We’ll run through some examples of these functions below.
Using JSON_TRANSFORM_STRICT() to Perform Strict JSON Transformations in DuckDB
DuckDB provides us with a json_transform_strict() function that works just like the json_transform() function, except that it throws an error when type casting fails. The json_transform() function on the other hand, returns NULL for the respective field in such cases.