duckdb conversion error: Could not convert string 'CHINA' to UNIT8

Question

I have the following inside my SQL query, which I pass to duckdb execute method.

df = self._con.execute(qry).df()

FROM 
    tbl 
WHERE 
    tbl.COUNTRY IN ('BRAZIL', 'INDIA', 'CHINA') 

If 'CHINA' in the list, I get the exception:

conversion error: Could not convert string 'CHINA' to UNIT8.

Note that the country column of my tbl, doesn't have CHINA. So I would assume it is related to that. But that should not throw an exception.

Without 'CHINA' the query executes fine. Any pointers would be appreciated. I am runnning python 3.10.10, duckdb 0.7.1, pandas 1.5.3 on a windows 11 machine.

Answer 1

That's because COUNTRY is an enum type in the tbl.

CREATE TYPE COUNTRY_TYPE AS ENUM ('BRAZIL', 'INDIA');
CREATE TABLE tbl (id int, country COUNTRY_TYPE); 

INSERT INTO tbl VALUES (1,'BRAZIL'), (2, 'INDIA'), (3, NULL);

✅

FROM tbl WHERE COUNTRY in ('BRAZIL', 'INDIA')

❌

FROM tbl WHERE COUNTRY in ('BRAZIL', 'INDIA', 'CHINA')

# Error: Conversion Error: Could not convert string 'CHINA' to UINT8

You can fix this by explicitly casting the enum type to varchar.

FROM tbl WHERE COUNTRY::VARCHAR in ('BRAZIL', 'INDIA', 'CHINA')