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

Example of Error

Suppose we create the following table:

-- Create a table for an online store's orders
CREATE TABLE orders (
    order_id INTEGER,
    customer_id INTEGER,
    product_name VARCHAR,
    quantity INTEGER,
    unit_price DECIMAL(10,2),
    discount DECIMAL(10,2),
    notes VARCHAR
);

-- Insert sample data with NULL values
INSERT INTO orders VALUES
    (1, 101, 'Laptop', 1, 999.99, NULL, 'Priority shipping'),
    (2, 102, 'Mouse', 2, 29.99, 5.00, NULL),
    (3, 103, 'Keyboard', NULL, 89.99, 10.00, 'Back-ordered'),
    (4, 104, 'Monitor', 1, NULL, NULL, NULL),
    (5, 105, 'Headphones', 2, 159.99, NULL, 'Gift wrap');

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

SELECT COALESCE(discount, 'none') FROM orders;

Result:

Conversion Error: Could not convert string "none" to DECIMAL(10,2)
LINE 1: SELECT COALESCE(discount, 'none') FROM orders;

This error occurred because the first value is a DECIMAL type, but our second value is a string. 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. When this happened, DuckDB tried to use the second value, but it couldn’t convert the value to DECIMAL(10,2), and the error was returned.

Solution 1

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

SELECT COALESCE(TRY_CAST(discount as VARCHAR), 'none') FROM orders;

Result:

+-------------------------------------------------+
| COALESCE(TRY_CAST(discount AS VARCHAR), 'none') |
+-------------------------------------------------+
| none                                            |
| 5.00                                            |
| 10.00                                           |
| none                                            |
| none                                            |
+-------------------------------------------------+

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.

For example, we could change the second argument to an integer:

SELECT COALESCE(discount, 0) FROM orders;

Result:

+-----------------------+
| COALESCE(discount, 0) |
+-----------------------+
| 0.00                  |
| 5.00                  |
| 10.00                 |
| 0.00                  |
| 0.00                  |
+-----------------------+

This time, any rows with a NULL value are returned as zero instead of none.