Explicit CAST — The Safe Conversion Toolkit

basic

ETL pipeline receives all data as strings from a CSV import — convert safely to proper types before inserting into the target schema.

-- Standard CAST: SQL-portable, works on all engines

SELECT

CAST('42' AS INTEGER) AS user_id,

CAST('123.45' AS DECIMAL(10,2)) AS price,

CAST('2024-01-15' AS DATE) AS order_date,

CAST('true' AS BOOLEAN) AS is_active, -- PostgreSQL

CAST(CURRENT_DATE AS VARCHAR(20)) AS today_string;

-- PostgreSQL :: shorthand (same result, shorter syntax)

SELECT

'42'::INTEGER AS user_id,

'123.45'::DECIMAL(10,2) AS price,

'2024-01-15'::DATE AS order_date,

employee_id::TEXT AS id_as_string

FROM employees;

-- TRY_CAST: Graceful failure for dirty data (SQL Server / Snowflake)

SELECT

raw_value,

TRY_CAST(raw_value AS INTEGER) AS parsed_int, -- NULL on failure

TRY_CAST(raw_value AS DECIMAL(10,2)) AS parsed_decimal,

CASE

WHEN TRY_CAST(raw_value AS INTEGER) IS NULL

THEN 'INVALID'

ELSE 'OK'

END AS validation_status

FROM raw_import_staging;

-- CAST results:

user_id | price | order_date | is_active | today_string

42 | 123.45 | 2024-01-15 | true | 2024-01-15

-- TRY_CAST results (dirty data):

raw_value | parsed_int | parsed_decimal | validation_status

'42' | 42 | 42.00 | OK

'abc' | NULL | NULL | INVALID

'12.5' | NULL | 12.50 | INVALID

'100' | 100 | 100.00 | OK

CAST is the SQL-standard conversion function — portable across all engines. PostgreSQL :: shorthand is concise but non-portable. TRY_CAST is essential for ETL pipelines: it returns NULL instead of crashing on bad data, letting you identify and quarantine invalid rows rather than failing the entire import.

All

DECIMAL vs FLOAT — Why Money Breaks with Float