SQL CAST and Type Conversion

CAST(value AS type) converts a value to another data type. DuckDB and PostgreSQL also accept the shorthand value::type, and DuckDB's TRY_CAST returns NULL for a value it can't convert instead of stopping the query.

select cast('42' as integer) as from_text,
       '42'::integer as shorthand,
       try_cast('forty-two' as integer) as try_failed

That returns 42, 42, and NULL.

When you need it

• Numbers or dates stored as text, which sort and compare as text until you convert them. Imported spreadsheets are the usual source.
• Exact money. A floating point price times a tax rate gives values like 30.240000000000002; a cast to DECIMAL(10,2) gives 30.24.
• Comparing two different types, such as a text column against a number.
• Formatting output, such as a date turned into text for a label.

Syntax

The type names you'll use most, as DuckDB spells them:

Text to numbers

Numbers kept as text sort as text, one character at a time, so '9' comes after '10'. Here Garden Shop's stock counts are turned into text, the way a careless import leaves them:

select product_name, quantity_on_hand::varchar as qty_text
from products
order by qty_text desc
limit 3

The "largest" counts come back as 90, 9, and 8. Cast back in the ORDER BY and the real top three, 200, 120, and 90, appear:

select product_name, quantity_on_hand::varchar as qty_text
from products
order by cast(qty_text as integer) desc
limit 3

CAST stops the whole query on the first value that isn't a number:

select cast('forty-two' as integer) as fails

DuckDB reports Conversion Error: Could not convert string 'forty-two' to INT32. An empty string fails the same way, while spaces around a number are fine (' 42 ' converts to 42). The could not convert string error page covers finding the bad rows in real data.

TRY_CAST: find what won't convert

TRY_CAST returns NULL where CAST would fail. Compare the two to list the values that don't convert, skipping the ones that were missing to begin with:

select phone
from customers
where phone is not null
  and try_cast(phone as integer) is null
order by phone

All 15 phone numbers on file come back, because 215-555-0142 has dashes. That's the right call: a phone number is a label, not a quantity, and belongs in text.

Numbers to integers: two rounding rules

Casting a fraction to an integer rounds it, but how depends on the type you start from. A DECIMAL or text value rounds half away from zero: cast(2.5 as integer) and cast('2.5' as integer) are both 3. A DOUBLE rounds half to the nearest even number, and Garden Shop's prices are doubles:

select product_name,
       price,
       cast(price as integer) as cast_int,
       cast(round(price) as integer) as rounded,
       cast(floor(price) as integer) as floored
from products
where price in (6.5, 7.5, 18.5)
order by price

The $6.50 pot casts to 6 and the $7.50 compost to 8. When the rule matters, say which one you want: round() rounds halves up (7, 8, 19), and floor() drops the fraction (6, 7, 18).

Numbers to DECIMAL

A cast to DECIMAL(p, s) rounds to s places: cast(123.456 as decimal(5,2)) is 123.46. A value with more digits before the point than p - s fails with Conversion Error: Casting value "12345.6" to type DECIMAL(5,2) failed: value is out of range!, and TRY_CAST returns NULL for it. Lesson 2.7 covers choosing between DECIMAL and DOUBLE.

Text to dates

A cast to DATE reads only the ISO form, YYYY-MM-DD:

select cast('20/04/2024' as date) as fails

DuckDB answers Conversion Error: invalid date field format: "20/04/2024", expected format is (YYYY-MM-DD). For any other layout, describe it to strptime:

select strptime('20/04/2024', '%d/%m/%Y')::date as parsed

The other direction, cast(order_date as varchar), gives text like 2024-01-15. See the date functions reference for strftime and other formats.

Text and numbers to booleans

DuckDB converts 'true', 't', 'yes', 'y', and '1' to true, and 'false', 'f', 'no', 'n', and '0' to false, in any letter case. Anything else, 'off' included, is a Conversion Error. From a number, 0 is false and every other number is true; in the other direction, true becomes 1 and false becomes 0.

Casts DuckDB makes for you

Comparing a column with a quoted literal converts the literal to the column's type. where order_date < '2024-04-20' compares dates, and where price > '9' compares numbers. The reverse is refused: a text column against a number is an error, not a guess.

select count(*)
from customers
where phone > 5

The message is Binder Error: Cannot compare values of type VARCHAR and type INTEGER_LITERAL - an explicit cast is required. Decide which type you mean, and cast that side.

What can go wrong

• Halves round differently by type. A DOUBLE cast to an integer rounds 6.5 down and 7.5 up. Use round() or floor() when the rule matters.
• TRY_CAST hides bad data. A failed conversion and a genuinely missing value both come back as NULL. Count the failures, as above, before you trust the converted column.
• A length on VARCHAR does nothing in DuckDB. 'abcdef'::varchar(3) is still abcdef. In PostgreSQL the same cast cuts it to abc.
• Casting doesn't fix a column, only one query. To change the stored type, use ALTER TABLE.

How other databases differ