· duckdb til

DuckDB 0.10: Binder Error: No function matches the given name and argument types

In the 0.10 version of DuckDB, a breaking change was made that stops implicit casting to VARCHAR during function binding. In this blog post, we’re going to look at some ways to work around this change when fixing our DuckDB code from 0.9 or earlier.

I have a CSV file that looks like this:

from 'people.csv' select *;
Output
┌─────────┬─────────────┐
│  name   │ dateOfBirth │
│ varchar │    int64    │
├─────────┼─────────────┤
│ John    │    19950105 │
└─────────┴─────────────┘

The dateOfBirth column isn’t an int64, but that’s how DuckDB has inferred it. I was then using the strptime function to convert the value to a TIMESTAMP:

from 'people.csv' select strptime(dateOfBirth, '%Y%m%d');

This results in the following error:

Output
Error: Binder Error: No function matches the given name and argument types 'strptime(BIGINT, STRING_LITERAL)'. You might need to add explicit type casts.
	Candidate functions:
	strptime(VARCHAR, VARCHAR) -> TIMESTAMP
	strptime(VARCHAR, VARCHAR[]) -> TIMESTAMP

LINE 1: from 'people.csv' select strptime(dateOfBirth, '%Y%m%d');

One way to fix this is to cast dateOfBirth, like this:

from 'people.csv' select strptime(dateOfBirth::STRING, '%Y%m%d') AS dob;
Output
┌─────────────────────┐
│         dob         │
│      timestamp      │
├─────────────────────┤
│ 1995-01-05 00:00:00 │
└─────────────────────┘

Alternatively, we could use the read_csv_auto function and specify the type for dateOfBirth via the types parameters:

from read_csv_auto('people.csv', types={"dateOfBirth": "STRING"})
select strptime(dateOfBirth, '%Y%m%d') AS dob;
Output
┌─────────────────────┐
│         dob         │
│      timestamp      │
├─────────────────────┤
│ 1995-01-05 00:00:00 │
└─────────────────────┘
  • LinkedIn
  • Tumblr
  • Reddit
  • Google+
  • Pinterest
  • Pocket