Convert a column's type in SQL (DuckDB)

When to use it

Every column that will be compared, summed or sorted as something other than text.

How

CAST(x AS type) stops on the first bad value; TRY_CAST returns NULL instead. When the rules give a pattern, check it with regexp_full_match before casting.

CASE WHEN regexp_full_match(amount, '-?[0-9]{1,10}(\.[0-9]{1,2})?') THEN CAST(amount AS DECIMAL(12,2)) END

• DECIMAL(p,s) is exact; DOUBLE is not.
• CAST(x AS TIMESTAMPTZ) converts offsets to the session time zone.

Pitfalls

• Letting a parser guess formats.
• Silently turning bad values into missing ones.
• Floating point for money.