DBMS

Database Management Systems

Find Out if a Table is WITHOUT ROWID in SQLite

by

One of SQLite’s unique features is the WITHOUT ROWID table, which can be used to optimize performance and storage in specific scenarios.

While it’s easy enough to create a WITHOUT ROWID table (just add WITHOUT ROWID to the definition), how to identify a WITHOUT ROWID table might not be so obvious.

In this article, we’ll start by briefly revising what WITHOUT ROWID tables are and how they differ from ordinary tables. Then we’ll look at how to identify these tables by using SQLite’s PRAGMA commands.

Read more

Fixing “Conversion Error” When Using COALESCE() in DuckDB

by

If you’re getting an error that reads “Conversion Error: Could not convert …etc” while using the COALESCE() function in DuckDB, it appears that you’re using arguments with incompatible types.

To fix this issue, try using CAST() or TRY_CAST() to ensure that all arguments are compatible. Alternatively, make sure the arguments to COALESCE() are of the same type (or at least, compatible types).

Read more

Using TRY_STRPTIME() to Handle Errors When Constructing Timestamps in DuckDB

by

If you’ve ever used the strptime() function to create a timestamp in DuckDB, you may be aware that it will return an error if it can’t construct the timestamp from the format string/s provided.

While such an error could be useful in some situations, it could also be annoying in others.

Fortunately, DuckDB also provides the try_strptime() function, which will suppress any error that we might ordinarily get in such cases. This function returns null instead of an error.

Read more

Using the AGE() Function to Compare Dates in DuckDB

by

When working with date and time data in DuckDB, calculating the difference between two dates is a common requirement. Whether we’re determining a person’s age from their birthdate or measuring the duration between two events, DuckDB’s age() function provides a straightforward solution. This function returns an interval representing the difference between two timestamps or dates, making it especially useful for time-based analyses.

In this article, we’ll explore how to use the age() function in DuckDB. We’ll cover its syntax, and provide some simple examples.

Read more

Fix ‘Conversion Error: extract specifier “monthname” not recognized’ in DuckDB

by

If you’re getting an error that reads “Conversion Error: extract specifier “monthname” not recognized” in DuckDB, it appears that you’re using a function like extract() or date_part() to try to get the month name from a date.

These functions don’t accept a monthname specifier, and so that’s why the error occurs. Fortunately, DuckDB provides a monthname() function, and so you could try that instead. Also, the strftime() function has a format specifier for the month name, and so that’s another option.

So to fix this issue, try the monthname() or strftime() function instead.

Read more

Checking if a Value is Finite in DuckDB with ISFINITE()

by

In DuckDB, ISFINITE() is a function for checking whether values are finite. DuckDB supports infinite values, and so we can use this function to check whether a value is infinite or not. This can be useful when working with floating-point data that might contain special values like NaN (Not a Number) or infinity. We can also use it on date and timestamp values.

Read more