CAST
CAST converts a value from one data type to another, such as turning a string into an integer or a timestamp into a date.
Overview
CAST(expression AS type) explicitly converts a value to a target data type. It's the standard ANSI SQL way to control type conversions rather than relying on implicit coercion, which varies between database engines and can silently produce unexpected results.
Copy code
SELECT CAST('42' AS INTEGER) AS as_int,
CAST(order_date AS DATE) AS just_the_date,
CAST(price AS VARCHAR) AS price_text;
DuckDB's :: shorthand
DuckDB (like PostgreSQL) supports a compact :: cast operator that's equivalent to CAST:
Copy code
SELECT '42'::INTEGER AS as_int,
amount::DECIMAL(10,2) AS amount_2dp;
Both forms produce identical results; :: is simply less verbose and popular in ad hoc queries, while CAST(... AS ...) is more portable across databases and often preferred in production SQL.
Failed casts
A CAST that cannot succeed -- for example, CAST('not a number' AS INTEGER) -- raises an error and aborts the query. When you want invalid conversions to produce NULL instead of failing, use TRY_CAST (DuckDB and several other engines) rather than CAST.
Casting complex types
Because DuckDB has rich nested types, CAST also works on lists, structs, and other composite types:
Copy code
SELECT CAST([1, 2, 3] AS DOUBLE[]) AS as_doubles;
When implicit casting kicks in
DuckDB will sometimes cast automatically -- for example, comparing an INTEGER column to a string literal, or inserting a VARCHAR value into a DATE column -- following its type coercion rules. Explicit CAST is preferred whenever the conversion isn't obvious, since it documents intent and avoids relying on engine-specific coercion behavior.
Related terms
TRY_CAST attempts to convert a value to another data type and returns NULL instead of raising an error if the conversion fails.
timestamps →Timestamps are a fundamental data type in databases and programming languages that represent a specific point in time, typically including both the date and…
COALESCE →COALESCE returns the first non-NULL value from a list of expressions, commonly used to supply default values or merge multiple possibly-NULL columns.
struct →A struct in DuckDB is a composite data type that allows you to group multiple fields of different types into a single unit.
Data cleansing →Data cleansing (or data cleaning) is the process of detecting and correcting inaccurate, incomplete, inconsistent, or malformed data so it's reliable for analysis and downstream systems.
data type →In the context of databases and programming, a data type defines the nature of data that can be stored in a specific field or variable.
FAQS
They're functionally identical -- CAST(expr AS type) is the ANSI SQL standard form, and expr::type is DuckDB/PostgreSQL shorthand for the same conversion.
CAST raises an error and the query fails; use TRY_CAST instead if you want failed conversions to return NULL rather than aborting the query.
Yes -- DuckDB supports casting between compatible nested types, such as casting a list of integers to a list of doubles, as long as the underlying element types are convertible.
