The Difference Between CAST() and TRY

DuckDB offers two primary functions for type conversion: cast() and try_cast(). While they serve similar purposes, their behavior when handling invalid conversions differs significantly, which can greatly impact our data processing workflows.

Basic Function

Both functions convert values from one data type to another:

cast(expression AS data_type) – Converts an expression to the specified data type. Returns an error if the conversion fails.
try_cast(expression AS data_type) – Converts an expression to the specified data type. Returns NULL if the conversion fails.

Main Differences

The distinction lies in how each function handles conversion failures:

Error Handling:
- cast() throws an error and terminates the query when conversion fails.
- try_cast() returns NULL and allows the query to continue executing.
Use Cases:
- cast() is ideal when you expect all values to be convertible.
- try_cast() excels when working with messy data where some values might not convert cleanly.

Examples

First, here’s an example of where both functions return the same result:

SELECT 
    cast(123.45 AS integer) AS cast,
    try_cast(123.45 AS integer) AS try_cast;

Output:

+------+----------+
| cast | try_cast |
+------+----------+
| 123  | 123      |
+------+----------+

Both functions successfully converted the value to an integer.

Now let’s look at an example where they differ.

When we use cast() to perform an invalid conversion, the conversion fails, and we get an error:

SELECT cast('abc' AS integer);

Output:

Conversion Error:
Could not convert string 'abc' to INT32

LINE 1: SELECT cast('abc' AS integer);
               ^

We can’t convert a string like 'abc' to an integer, and so the error is expected.

However, using try_cast() will produce a different result:

SELECT try_cast('abc' AS integer);

Output:

NULL

This function returned NULL instead of an error. This can be handy for situations where we need to convert multiple values in the same query, and we don’t want one or two failed conversions to prevent the success of the valid conversions.

Real-World Application

Suppose we create and populate the following table:

-- Create the product_imports table
CREATE TABLE product_imports (
    product_id VARCHAR(10),
    product_name VARCHAR(100),
    price VARCHAR(20),  -- Using VARCHAR to allow mixed formats
    category VARCHAR(50),
    import_date DATE
);

-- Insert sample data with various price formats to demonstrate CAST vs TRY_CAST
INSERT INTO product_imports VALUES
    ('P001', 'Wireless Headphones', '49.99', 'Electronics', '2025-04-15'),
    ('P002', 'Coffee Maker', '129.95', 'Kitchen Appliances', '2025-04-16'),
    ('P003', 'Yoga Mat', '24.50', 'Fitness', '2025-04-16'),
    ('P004', 'Desk Lamp', 'N/A', 'Home Office', '2025-04-17'),  -- Non-numeric price
    ('P005', 'Bluetooth Speaker', '39.99', 'Electronics', '2025-04-17'),
    ('P006', 'Water Bottle', '12.99', 'Fitness', '2025-04-18'),
    ('P007', 'Desk Chair', '$199.95', 'Home Office', '2025-04-18'),  -- Price with currency symbol
    ('P008', 'External Hard Drive', '79.99', 'Electronics', '2025-04-19'),
    ('P009', 'Blender', 'on sale', 'Kitchen Appliances', '2025-04-19'),  -- Text instead of price
    ('P010', 'Running Shoes', '89,95', 'Fitness', '2025-04-20');  -- Comma as decimal separator

-- Display the table contents
SELECT * FROM product_imports;

Result:

+------------+---------------------+---------+--------------------+-------------+
| product_id |    product_name     |  price  |      category      | import_date |
+------------+---------------------+---------+--------------------+-------------+
| P001       | Wireless Headphones | 49.99   | Electronics        | 2025-04-15  |
| P002       | Coffee Maker        | 129.95  | Kitchen Appliances | 2025-04-16  |
| P003       | Yoga Mat            | 24.50   | Fitness            | 2025-04-16  |
| P004       | Desk Lamp           | N/A     | Home Office        | 2025-04-17  |
| P005       | Bluetooth Speaker   | 39.99   | Electronics        | 2025-04-17  |
| P006       | Water Bottle        | 12.99   | Fitness            | 2025-04-18  |
| P007       | Desk Chair          | $199.95 | Home Office        | 2025-04-18  |
| P008       | External Hard Drive | 79.99   | Electronics        | 2025-04-19  |
| P009       | Blender             | on sale | Kitchen Appliances | 2025-04-19  |
| P010       | Running Shoes       | 89,95   | Fitness            | 2025-04-20  |
+------------+---------------------+---------+--------------------+-------------+

Here’s an example of a query we might run against that table:

SELECT 
    product_id,
    price,
    try_cast(price AS DECIMAL(10,2)) AS clean_price
FROM product_imports
ORDER BY clean_price DESC NULLS LAST;

Result:

+------------+---------+-------------+
| product_id |  price  | clean_price |
+------------+---------+-------------+
| P002       | 129.95  | 129.95      |
| P008       | 79.99   | 79.99       |
| P001       | 49.99   | 49.99       |
| P005       | 39.99   | 39.99       |
| P003       | 24.50   | 24.50       |
| P006       | 12.99   | 12.99       |
| P004       | N/A     | NULL        |
| P007       | $199.95 | NULL        |
| P009       | on sale | NULL        |
| P010       | 89,95   | NULL        |
+------------+---------+-------------+

This query handles cases where some price values might not be valid decimal numbers, placing NULL values at the end of the results instead of failing. Importantly, we use try_cast() to perform the conversions. This ensures that the query results are still returned, even when not all rows were able to be successfully converted.

By contrast, here’s what happens when we use cast() to perform the conversions:

SELECT 
    product_id,
    price,
    cast(price AS DECIMAL(10,2)) AS clean_price
FROM product_imports
ORDER BY clean_price DESC NULLS LAST;

Result:

Conversion Error:
Could not convert string "N/A" to DECIMAL(10,2)

LINE 4:     cast(price AS DECIMAL(10,2)) AS clean_price
            ^

We get an error. Not only that, none of the successful conversions are returned.

When to Use Each Function

Use cast() when:
- You need to ensure data integrity
- Invalid conversions should fail the query
- Performance is critical (slightly faster than try_cast())
Use try_cast() when:
- Working with potentially messy data
- You want to handle conversion failures gracefully
- You prefer nulls over query failures

Conclusion

The choice between cast() and try_cast() in DuckDB depends on your data quality expectations and error handling preferences. cast() prioritizes strict data integrity, while try_cast() offers resilience when working with imperfect data.