CAST()

Syntax

How it works

CAST(expression AS type) takes a value and produces the same value expressed as a different data type. It is the ANSI SQL standard conversion function, so the exact same syntax runs on MySQL, PostgreSQL, SQL Server and SQLite. That portability is the main reason to reach for CAST() rather than an engine specific function.

Typical jobs include turning text that holds digits into a real number, turning a number into text so it can be concatenated with other strings, and turning a string such as '2026-07-04' into a proper DATE. A very common use is forcing decimal division: dividing two integers gives integer division in many engines, so you cast one side to DECIMAL first to keep the fractional part.

PostgreSQL adds a shorthand operator, ::, so value::int means exactly the same as CAST(value AS int). It is convenient but not portable, so prefer CAST() in code that must run on more than one engine. SQL Server offers its own CONVERT() function, which adds style codes for formatting dates and numbers.

Examples

Convert text to an integer

-- code is stored as text but holds digits
SELECT CAST('42' AS INT) AS as_number;

as_number
---------
       42

Avoid integer division by casting to DECIMAL

-- passed / total are integers, so passed / total
-- would truncate to 0. Cast one side first.
SELECT passed,
       total,
       CAST(passed AS DECIMAL(10,2)) / total AS pass_rate
FROM exam_results;

passed | total | pass_rate
-------+-------+----------
    18 |    24 |      0.75
     7 |    10 |      0.70

Convert a string to a DATE

SELECT CAST('2026-07-04' AS DATE) AS order_date;

order_date
----------
2026-07-04

Convert a number to text for concatenation

-- glue an id onto a label; the number must
-- become text before it can be concatenated
SELECT 'INV-' || CAST(invoice_id AS VARCHAR(10)) AS ref
FROM invoices;

ref
--------
INV-1001
INV-1002
INV-1003

PostgreSQL :: shorthand

-- these two expressions are identical in PostgreSQL
SELECT '3.14'::numeric        AS with_shorthand,
       CAST('3.14' AS numeric) AS with_cast;

with_shorthand | with_cast
---------------+----------
          3.14 |      3.14

Common mistakes

-- Errors: 'N/A' is not a number, so the whole
-- query fails on the first bad row
SELECT CAST(amount AS DECIMAL(10,2))
FROM payments;

-- SQL Server: TRY_CAST returns NULL instead of
-- failing, so bad values do not abort the query
SELECT TRY_CAST(amount AS DECIMAL(10,2))
FROM payments;

-- Truncates: DECIMAL(10,0) has no decimal places,
-- so 19.99 silently becomes 20
SELECT CAST(19.99 AS DECIMAL(10,0)) AS price;

-- Keep the scale you actually need
SELECT CAST(19.99 AS DECIMAL(10,2)) AS price;

-- Casting the indexed column stops the engine
-- from using the index on user_id
SELECT *
FROM orders
WHERE CAST(user_id AS VARCHAR(20)) = '1001';

-- Cast the literal, or match the real type, so the
-- index on user_id can still be used
SELECT *
FROM orders
WHERE user_id = 1001;

Performance

A CAST() on a literal or a computed value is essentially free. The cost that matters is casting a column inside a WHERE or JOIN condition, because the database must convert every row before it can compare, and that hides any usable index behind the function call.

The fix is to cast the other side. Instead of CAST(order_id AS VARCHAR) = '1001', compare the column to a value of its own type: order_id = 1001. When two columns are joined, store and compare them in the same type so the optimiser never has to convert on the fly, which also avoids implicit conversions that silently degrade to a scan.

For repeated heavy conversions on the same expression, consider a computed or generated column of the target type with its own index, so the cast is done once at write time rather than on every read. See the indexing guide for how sargable predicates keep queries fast.

Interview questions

SELECT CAST(passed AS DECIMAL(10,2)) / total AS pass_rate
FROM exam_results;