CAST
CAST converts a value to another data type inside a query.
In this page:
Syntax
SELECT CAST(value AS type);
SELECT value::type;CAST
CAST(value AS type) works everywhere, and PostgreSQL also offers value::type. Common uses are turning text into numbers or dates, numbers into text for concatenation, and decimals into integers.
Explicit casts show intent and avoid surprising implicit conversions.
Note:
:: is shorter, but CAST is portable across databases.
Example: CAST
CREATE TABLE raw (id INTEGER PRIMARY KEY, amount_text TEXT);
INSERT INTO raw VALUES (1, '12'), (2, '7'), (3, '100');
SELECT SUM(CAST(amount_text AS INTEGER)) AS total FROM raw;
SELECT id, amount_text < '7' AS text_compare, CAST(amount_text AS INTEGER) < 7 AS number_compare FROM raw ORDER BY id;
SELECT CAST(9 AS REAL) / 4 AS ratio;
-- Output:
-- total
-- 119
-- id | text_compare | number_compare
-- 1 | 1 | 0
-- 2 | 0 | 0
-- 3 | 1 | 0
-- ratio
-- 2.25Login to try C/C++/Java/PHP code in the editor
Related Topics
Common Mistakes
- Casting strings that are not valid numbers
- Assuming casts round instead of truncating
- Ignoring precision loss
Chapter Summary
- CAST(x AS type) or x::type
- Text to number requires valid text
- Numeric to integer truncates or rounds by type
- Cast explicitly for clarity
🔒
Chapter Quiz — Complete all 7 topics to unlock
0/7 topics done
Complete these topics first: