CAST
Explicit type conversion. CAST(expr AS type) (standard SQL) and expr::type (Postgres/DuckDB shorthand) behave the same. Most useful when reading untyped text (CSV, JSON), forcing integer division to return a decimal, or bounding numeric precision for financial math.
Syntax #
SELECT
CAST(expr AS target_type),
expr::target_type -- Postgres / DuckDB shorthand
FROM table_name;Example #
Silent precision loss and rounding differences between types. Casting DOUBLE → INTEGER truncates toward zero (not FLOOR). Casting DECIMAL(12,4) → DECIMAL(12,2) rounds, and the rounding mode depends on the engine. For money, pick DECIMAL(p,s) from the start; never store amounts in FLOAT or DOUBLE. For messy text-to-number parsing, prefer TRY_CAST (DuckDB, SQL Server) or CAST ... AS ... ON ERROR patterns — they return NULL on failure instead of aborting the whole query, letting you isolate bad rows.