Tag: how to

Using TRY_CAST() to Handle Errors When Converting Between Data Types in DuckDB

Posted on by Ian

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.

Continue reading →

Fix “No function matches the given name and argument types ‘list_concat…” When Using array_push_front() or array_push_back() in DuckDB

Posted on by Ian

If you’re getting a binder error that reads something like “No function matches the given name and argument types ‘list_concat(STRING_LITERAL, VARCHAR[][])’. You might need to add explicit type casts.” in DuckDB when using either the array_push_front() or array_push_back() functions, it could be due to a slight syntax error.

Continue reading →

2 Ways to Check if a Table has any Partial Indexes in SQLite

Posted on by Ian

In SQLite, a partial index includes only the rows that meet a specified condition, set by a WHERE clause in the index definition. This is different from a regular, or “full,” index, which covers all rows in a table. Partial indexes are helpful when you frequently query specific subsets of data, as they can improve query performance and reduce storage.

If you ever need to check a table to see if it contains any partial indexes, you can use either of the following methods.

Continue reading →

4 Ways to Get the ISO Year from a Date in DuckDB

Posted on by Ian

In the ISO-8601 calendar, years begin on the first Monday closest to January 1, meaning the start date can fall between December 29 and January 4. This differs from the Gregorian calendar, where years always start on January 1, leading to potential discrepancies around these dates. Additionally, ISO-8601 years, or just ISO years, can be either 52 or 53 weeks long, depending on their starting point.

This article provides four options for extracting the ISO year from a date in DuckDB, accounting for these unique calendar rules.

Continue reading →