DBMS – Page 29

Using LIST_REVERSE() to Reverse the Items in a List in DuckDB

April 20, 2025April 19, 2025 by Ian

Deep amongst DuckDB’s vast selection of list functions lies the list_reverse() function. This function reverses the elements in a list. We simply pass the list to the function and it reverses the order of the elements in that list.

Not to be confused with the list_sort() or list_reverse_sort() functions, the list_reverse() function reverses the order of the items as they currently sit in the list. The ...sort() functions on the other hand, actually perform a sort operation on the list items.

A Look at DuckDB’s LIST_SORT() Function

April 18, 2025 by Ian

DuckDB has a list_sort() function that does exactly what its name promises; sorts lists.

While the easiest way to use this function is to simply pass a list, we can also pass other arguments to fine-tune the results.

Suppressing Query Output in the DuckDB CLI

April 17, 2025 by Ian

DuckDB’s command-line interface (CLI) provides a way to suppress query results using the .mode trash command. This feature is particularly useful in scenarios where you need to execute queries but don’t want their results to be displayed.

Working with Intervals in DuckDB

May 7, 2025April 16, 2025 by Ian

Time-based operations are a fundamental aspect of data analysis and manipulation. DuckDB, the in-process analytical database management system, provides robust support for handling time-based operations through its INTERVAL data type.

This article explores the capabilities, syntax, and practical applications of intervals in DuckDB.

2 Ways to Return the Number of Rows Changed by a SQL Statement in SQLite

April 15, 2025 by Ian

SQLite provides several ways to determine how many rows are affected by SQL statements such as INSERT, UPDATE or DELETE.

One way of achieving this is with the changes() function. This function returns the number of rows modified by the most recent SQL statement executed in the current session.

Another way is with the .changes dot command.

In this article, we’ll look at an example that uses these options to get the number of rows changed by various SQL statements.

Using DATE_PART() to Extract Date and Time Components in DuckDB

April 14, 2025 by Ian

DuckDB provides us with a good selection of functions for working with date/time values. Among them is date_part(), which we can use to extract specific components from dates, timestamps, and intervals.

In this article, we’ll look how the date_part() function works, along with some basic examples.

Fix “Out of Range Error” When Using GENERATE_SUBSCRIPTS() in DuckDB

April 13, 2025 by Ian

If you’re getting an “Out of Range Error” when using the generate_subscripts() function in DuckDB, it could be that you’re specifying a non-existent dimension for the array.

DuckDB’s generate_subscripts() function accepts the array as the first argument, and the dimension as the second argument. The second argument must correspond to an actual dimension present in the array.

To fix this issue, be sure to specify a dimension that actually exists in the array.

Looking at DuckDB’s STRING_AGG() Function

April 12, 2025 by Ian

DuckDB has a string_agg() function, which allows us to concatenate strings from a group of rows into a single string. This function can be useful when we need to aggregate text data in a meaningful way.

In this article, we’ll explore how the string_agg() function works, along with some simple examples to demonstrate its usage.

Removing Duplicates from a List in DuckDB with the LIST_DISTINCT() Function

April 11, 2025 by Ian

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.

DuckDB Now Ships With a Web User Interface

April 10, 2025 by Ian

DuckDB has enhanced its user experience with the introduction of a new web-based user interface in the v1.2.1 release. This addition brings graphical interaction capabilities to the analytical database system, making it more accessible to users who prefer visual interfaces over command-line operations.