Type casting
Casting converts a value from one type to another with CAST or the :: shortcut.
In this page:
Syntax
SELECT CAST(expression AS type);
SELECT expression::type;Type casting
CAST(expr AS type) is standard SQL and works in every database, while expr::type is PostgreSQL's shorter form. Casting text to a number fails if the text is not a valid number.
Integer division truncates, so cast one side to a decimal type when you need a fractional result. The example uses CAST so it also runs on SQLite.
Note:
In PostgreSQL, 42::integer and CAST(42 AS integer) are equivalent.
Example: Type casting
SELECT CAST('42' AS INTEGER) + 8 AS sum_int;
SELECT 7 / 2 AS integer_division, CAST(7 AS REAL) / 2 AS decimal_division;
SELECT CAST(3.99 AS INTEGER) AS truncated;
SELECT CAST(12345 AS TEXT) || ' items' AS label;
-- Output:
-- sum_int
-- 50
-- integer_division | decimal_division
-- 3 | 3.5
-- truncated
-- 3
-- label
-- 12345 itemsLogin to try C/C++/Java/PHP code in the editor
Related Topics
Common Mistakes
- Dividing integers and expecting decimals
- Casting invalid text to numbers
- Relying on implicit conversions
Chapter Summary
- CAST(expr AS type) is standard
- :: is the PostgreSQL shortcut
- Invalid text fails to cast
- Cast before dividing for decimals
🔒
Chapter Quiz — Complete all 7 topics to unlock
0/7 topics done
Complete these topics first: