SQL / TEXT, NUMERIC, AND DATE FUNCTIONS
Casting values and converting between types
Convert values between text, numeric, and date types with CAST, control what happens when a conversion fails, and predict rounding and overflow surprises.
What you will learn
- Write CAST(expr AS type) for portable conversion; :: is the PostgreSQL shorthand
- Turn failing text-to-number casts into NULL with TRY_CAST, SAFE_CAST, or a CASE guard
- Cast the literal, not the indexed column, so the comparison stays index-friendly
- Predict rounding, silent truncation, and overflow when the target type is narrower
Understanding Casting values and converting between types
Every value in a SQL statement already has a type, and that type is the rulebook the engine uses to compare, sort, calculate, and display it. CAST(expr AS type) does not relabel a value; it builds a brand-new value of the target type that stands for the same thing under the new rulebook, which is why text-to-number conversion is really parsing and number-to-text conversion is really formatting. PostgreSQL's expr::type, SQL Server's CONVERT(type, expr), and Oracle's TO_NUMBER are shorthands for that same operation, so CAST is the spelling to reach for when the query may move to another engine.
When you mix types in one expression the engine does not refuse; it inserts casts for you according to type-precedence rules, usually widening the narrower type, so 1 + 2.0 comes back as numeric rather than integer. The same rules explain why a text column holding '2', '10', '9' sorts as 10, 2, 9: the comparison follows text rules because the values are text, no matter how numeric they look. Making the conversion explicit is how you pick the rulebook instead of inheriting it, but placement matters: casting a literal costs nothing, while casting a column forces a conversion per row and hides the column from its index.
Two different things can go wrong in a conversion. If the source is not a valid value of the target type, PostgreSQL and SQL Server raise an error and abandon the whole statement, MySQL in non-strict mode returns 0 with a warning, and TRY_CAST, TRY_CONVERT, or SAFE_CAST turn the failure into NULL so the good rows survive. If the value is valid but the target is narrower, information quietly disappears instead: numeric(6,2) rounds away the third decimal, smallint overflows, decimal-to-integer rounds on some engines and truncates on others, and text-to-date leans on the session's date style, so state your intent with ROUND or an explicit format function rather than letting the cast decide for you.
CREATE TABLE reading (sensor text, raw_value text);
INSERT INTO reading VALUES ('a', '19.90'), ('b', '204'), ('c', ' 7.25 ');
SELECT sensor,
CAST(raw_value AS numeric) AS value_num,
CAST(raw_value AS numeric) * 2 AS doubled,
CAST(CAST(raw_value AS numeric) AS integer) AS as_int
FROM reading
ORDER BY sensor;A cast produces a new value under the target type's rules, and those rules, not the way the value looks, decide how it compares, how it calculates, and whether the conversion fails at all.
Worked examples
One bad row versus a guarded cast
Shows that a failing cast destroys the entire result set, and how a pattern guard converts only the rows that can be converted.
CREATE TABLE staging (amount_text text);
INSERT INTO staging VALUES ('99'), ('99 apples'), ('-7');
SELECT amount_text, CAST(amount_text AS integer) AS strict_cast FROM staging;
SELECT amount_text,
CASE WHEN amount_text ~ '^\s*-?\d+\s*$'
THEN CAST(amount_text AS integer) END AS guarded_cast
FROM staging;Example explained
Line 1The integer input parser must consume the whole string, so the leftover 'apples' aborts the statement and the two convertible rows are thrown away with it.
Line 2The pattern ^\s*-?\d+\s*$ allows surrounding whitespace and an optional sign, which is exactly what the integer parser accepts, so the guard and the cast agree on what is valid.
Line 3CASE evaluates only the branch it selects, so the cast never runs for '99 apples'; the missing ELSE yields NULL, which is why that cell prints blank.
Line 4SQL Server writes this as TRY_CAST(amount_text AS int) and BigQuery as SAFE_CAST; PostgreSQL has no TRY_CAST, though 16 added pg_input_is_valid for the same check.
The cast decides the comparison rules
Demonstrates that sorting digit strings changes completely depending on whether the values are treated as text or as integers.
CREATE TABLE part (code text);
INSERT INTO part VALUES ('2'), ('10'), ('9');
SELECT code FROM part ORDER BY code;
SELECT code FROM part ORDER BY code::integer;Example explained
Line 1code is text, so ORDER BY code compares character by character and '1' lands before '2', putting '10' ahead of '2'.
Line 2code::integer is PostgreSQL shorthand for CAST(code AS integer); it changes which comparison rules apply, not the data stored in the table.
Line 3The second sort converts every row at query time and produces values no index on code describes, so the real fix for a live table is to store the column as integer.
Narrowing: rescaling, rounding, overflow
Shows what happens when the target type cannot hold everything the source value carries.
SELECT CAST(2.345 AS numeric(5,2)) AS rounded_to_scale,
CAST(2.345 AS integer) AS to_integer,
CAST(2.345 AS text) || 'kg' AS to_text;
SELECT CAST(70000 AS smallint) AS overflow;Example explained
Line 1numeric(5,2) allows two digits after the point, so the third decimal is rounded away rather than chopped off: 2.345 becomes 2.35.
Line 2The integer cast rounds in PostgreSQL and MySQL; because engines disagree on that, say ROUND or FLOOR explicitly when the direction matters.
Line 3Casting numeric to text yields the same digit string you see in the result, which is what lets || attach the unit.
Line 470000 is a perfectly good integer but exceeds smallint's 32767 limit, so the conversion is well-formed yet impossible, and PostgreSQL errors instead of wrapping around.
Important notes
In PostgreSQL :: binds tighter than every operator, so total + tax::numeric converts only tax; write CAST(total + tax AS numeric) when you mean the sum.
CAST('abcdef' AS varchar(3)) quietly returns 'abc' in PostgreSQL and SQL Server, so casting to a length-limited type is a shortening operation, not a validation step.
Common mistakes
Assuming a decimal-to-integer cast truncates: CAST(9.99 AS integer) gives 10 in PostgreSQL and MySQL but 9 in SQL Server and SQLite, so the same report totals differ per engine.
Casting text that still carries formatting: '$1,200'::numeric and '1,200'::numeric both fail outright, so a data load aborts partway through instead of skipping the row.
Writing WHERE CAST(order_id AS text) = '4711': the rows are right, but the index on order_id is no longer usable and the lookup becomes a full table scan.
Try it yourself
Change, predict, then run
In a browser editor, create a one-column text table holding '12', ' 8 ', '3.5' and 'n/a', then write a SELECT that shows each raw value beside its numeric value with NULL for 'n/a'. Add a second query whose SUM over the converted column returns 23.5.
Open the SQL workspaceCheck your understanding
orders.order_id is an integer column with an index on it, and a report filters rows with WHERE CAST(order_id AS text) LIKE '47%'. What does that cast actually change?
- Nothing meaningful: the engine drops the cast because both sides end up compared as numbers anyway.
- CAST is only permitted in the SELECT list, so the statement fails to parse.
- Every row's order_id must be converted before the comparison, so the index cannot be used and the test now matches any id whose digits start with 47, including 4711 and 470000.
- The cast makes the comparison type-safe, so the index is still used and each row is simply checked twice.
Show answer
A cast applied to the column produces a derived value that the index does not store in that order, so the planner falls back to scanning and converting every row, and the meaning changes as well: '47%' is a text prefix test, not a numeric range. Option 0 is tempting because implicit conversion usually makes mixed-type comparisons work by promoting the literal, but an explicit cast overrides that choice: you told the engine to compare text, and it obeys.