If you’re getting an error that reads “AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY” in SQLite, it appears that you’re trying to define a column as an AUTOINCREMENT on a column that’s not defined as INTEGER PRIMARY KEY.
SQLite only allows us to use AUTOINCREMENT on INTEGER PRIMARY KEY columns.
To address this issue, be sure to make the column an INTEGER PRIMARY KEY if you need to use AUTOINCREMENT on it.
In DuckDB, the strptime() function converts a date/time string into a valid timestamp value. We pass a format string to the function in order to tell it what format our string uses. This can be handy if we ever need to construct timestamps based on date/time strings that may or may not be in a valid format.
But what if we have multiple date/time strings in different formats?
Fortunately, the strptime() function caters for this scenario too.
Usually when we run a SQL query it’s important to know the data type of the columns returned by that query. If we’re familiar with the data, then we might instinctively know the data type of each column. But even then, we might only have a general idea, such as “it’s a date” or “it’s a number” without knowing the exact details.
Fortunately, DuckDB provides us with several ways to find out the data type of the columns returned by a query.
The json_group_array() function in DuckDB is used to aggregate values into a JSON array, making it especially useful when working with structured or semi-structured data such as JSON. This function is part of DuckDB’s JSON extension and works similarly to DuckDB’s string_agg() or the group_concat() function in some other RDBMSs, but instead of returning a delimited string, it returns a well-formed JSON array.
This function is particularly helpful when we need to represent grouped or hierarchical data in a JSON format for export, reporting, or further transformation.
When working with SQL databases, one operation we often find ourselves performing is date/time arithmetic, such as adding or subtracting an interval to/from a date/time value. Fortunately, most RDBMSs make such operations quite easy to achieve, and DuckDB is no exception.
Below are two methods we can use in order to subtract hours from a date, timestamp, or time value in DuckDB.
When working with dates in DuckDB, sometimes we need to extract date parts from date or timestamp values. And when it comes to date parts like days and months, we have the option of getting the numeric representation or the actual name. And if we want the name, we have a further option of getting the full name or the shortened version.
For example, we can get December or we can get Dec.
Weighted averages are common calculations in data analysis, allowing us to assign different levels of importance to individual values in our dataset. Unlike simple averages, where each value has equal impact, weighted averages let us incorporate the relative significance of each observation. This is particularly valuable for scenarios like calculating GPA (where courses have different credit weights), investment portfolio returns (where assets have varying allocations), or quality ratings (where reviewers have different expertise levels).
In this article, we’ll explore three ways of calculating weighted averages in DuckDB.
DuckDB makes it easy to export query results to JSON format, which can help us integrate database outputs with web applications, APIs, and data processing pipelines. This article explores how to generate JSON files from DuckDB queries.
The json_transform() function in DuckDB is a handy tool for converting JSON strings into structured data types like STRUCT, MAP, and LIST. This allows you to directly query and manipulate nested JSON data using standard SQL, making it much easier to work with complex JSON objects and arrays.
Think of it as a way to cast your JSON data into a more usable, typed format within your database.
DuckDB has a bunch of functions that allow us to extract data from JSON documents. For example, there’s the json_extract() function, which extracts JSON from the specified JSON document.
Often times we’ll need to extract multiple values within the same query. For example, we may need to extract both a user’s name and age, so that they’re returned in two separate columns.