If you’re getting an error that reads something like “Binder Error: Cannot mix values of type …etc” when using the COALESCE() function in DuckDB, it’s probably because 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).

Example of Error

Suppose we create the following table:

-- Create a sample table
CREATE TABLE products (
    id INTEGER,
    price DECIMAL(10,2),
    discount VARCHAR,
    notes VARCHAR
);

-- Insert sample data
INSERT INTO products VALUES
    (1, 99.99, '10.00', NULL),
    (2, 149.99, NULL, 'Sale item'),
    (3, 199.99, '15.50', 'Clearance'),
    (4, 299.99, 'Special', NULL);

Here’s an example of code that produces the error:

SELECT COALESCE(discount, 0) FROM products;

Result:

Binder Error: Cannot mix values of type VARCHAR and INTEGER_LITERAL in COALESCE operator - an explicit cast is required

This error occurred because the first value is string, but our second value is an integer. The COALESCE() function returns the first non-NULL value from its arguments. So in this case, the second argument would be used only when the first argument (i.e. the discount column) contains NULL. However, for this to occur, both arguments would need to be of compatible types. As the error message states, we cannot mix values of type VARCHAR and INTEGER_LITERAL.

It’s not just the INTEGER_LITERAL. We’d get the same error if we were mixing the string with another numeric type, such as DECIMAL. For example, using the price column as the second argument also causes an error:

SELECT COALESCE(discount, price) FROM products;

Result:

Binder Error: Cannot mix values of type VARCHAR and DECIMAL(10,2) in COALESCE operator - an explicit cast is required

Solution 1

To fix this issue, we can use CAST() or TRY_CAST() to convert the discount column to a string:

SELECT COALESCE(discount, TRY_CAST(0 AS VARCHAR)) FROM products;

Result:

+--------------------------------------------+
| COALESCE(discount, TRY_CAST(0 AS VARCHAR)) |
+--------------------------------------------+
| 10.00                                      |
| 0                                          |
| 15.50                                      |
| Special                                    |
+--------------------------------------------+

This time it ran without error.

Solution 2

Another way to deal with the error is to make sure the arguments are the same type, or of types that can be implicitly converted.

Here’s an example of passing the second argument as a string:

SELECT COALESCE(discount, '0') FROM products;

Result:

+-------------------------+
| COALESCE(discount, '0') |
+-------------------------+
| 10.00                   |
| 0                       |
| 15.50                   |
| Special                 |
+-------------------------+

In this case, the query ran without any need for an explicit conversion due to the fact that both arguments were already strings.