When it comes to working with lists, DuckDB often provides us with multiple methods of performing a given task. Getting the last value from a list is no exception.
Here are four options we can use to extract the last value from a list in DuckDB.
Quick Overview of the List Operators in DuckDB
DuckDB provides us with a bunch of functions and operators that we can use when working with lists. These include operators for doing things like concatenating lists to performing calculations on the lists. Most of these operators are aliases for a corresponding function, so we can choose which one to use in those cases.
This article provides an overview of the six list operators available in DuckDB, along with basic examples to demonstrate their usage.
Subtract Months from a Date in DuckDB
If you find yourself in the situation where you need to subtract a number of months from a date in DuckDB, here are two options for you.
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.
2 Ways to Get the Month Name from a Date in DuckDB
DuckDB offers a pretty good range of functions that enable us to get date parts from date or timestamp value. For example, we can extract the month part from a given date value. In most cases, this will be the month number, for example 08 or just 8.
But sometimes we might want to get the actual month name, like October for example. And other times we might just want the abbreviated month name, like Oct.
Fortunately, DuckDB’s got our back. Here are two ways to return the month name from a date or timestamp value in DuckDB.
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.
Generating Date Ranges with DuckDB Queries
Working with date ranges is a common requirement in data analysis, reporting, and time-series operations. DuckDB provides us with several approaches for generating date ranges. This article explores various techniques for creating date ranges in DuckDB.