Tag: how to

Fix ‘Binder Error: Could not find key “…” in struct’ When Using DuckDB’s ARRAY_EXTRACT() Function

Posted on by Ian

If you’re getting an error that reads something like “Binder Error: Could not find key “…” in struct” when using the array_extract() function in DuckDB, it could be that you’re specifying a non-existent key.

When using DuckDB’s array_extract() function to extract a value from a struct, we must provide a key that actually exists in the struct. Otherwise we’ll end up with an error like the above one.

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

Continue reading →

Flattening Nested Lists with DuckDB’s FLATTEN() Function

Posted on by Ian

DuckDB has a flatten() function that we can use to flatten nested lists. The function concatenates a list of lists into a single list. So whether the outer list contains just one list or multiple lists, we can use the flatten() function to flatten them into one list.

However, it only goes one level deep, so that’s something to keep in mind.

Continue reading →

Fix “Could not convert string ‘…’ to INT64” When Using the GENERATE_SUBSCRIPTS() Function in DuckDB

Posted on by Ian

If you’re getting an error that reads something like “Could not convert string ‘…’ to INT64” when using the generate_subscripts() function in DuckDB, it appears that your second argument is a string, when it should be an integer.

DuckDB’s generate_subscripts() function accepts two arguments; the array as the first argument, and the dimension as the second argument. The second argument must be INT64 (or be able to be implicitly converted to that type). Passing the wrong data type as the second argument can cause the above error to occur.

To fix this issue, make sure that the second argument is compatible with INT64.

Continue reading →

Adding Quotes Around Field Values When Outputting DuckDB Query Results as a List

Posted on by Ian

In this article we look at how to automatically enclose values in single quotes when outputting DuckDB query results as a list. Using this method, each value is formatted as SQL literals. This can be useful when you want to escape the values for SQL or for some other purpose.

We also look at how CSV output is sometimes quoted with double quotes, and how we can adjust the results by doing things like removing the headers and changing the list separator.

Continue reading →

Sampling Rows from a Table in DuckDB with the SAMPLE Clause

Posted on by Ian

DuckDB’s SAMPLE clause is a handy feature that allows us to work with a random subset of our data. This is particularly useful when dealing with large datasets where processing the entire dataset might be time-consuming or unnecessary for exploratory data analysis, testing queries, or creating representative samples.

When we use this clause, we can specify the absolute number of rows to return, or a percentage of rows. We also have an option of sampling method to use.

Continue reading →

Find Out if a Table is WITHOUT ROWID in SQLite

Posted on by Ian

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.

Continue reading →

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

Posted on by Ian

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).

Continue reading →