Type Casting (CAST / ::)
beginneradvanced
2 min read
What is Type Casting?
Type casting converts a value from one data type to another. PostgreSQL supports both the SQL-standard CAST() function and the PostgreSQL-specific :: operator.
Syntax
-- SQL standard
SELECT CAST(value AS target_type);
-- PostgreSQL shorthand
SELECT value::target_type;When to Use
- Converting strings to numbers or dates
- Formatting numbers for display (e.g., rounding with NUMERIC)
- Comparing values of different types
- Converting aggregated results for arithmetic
Key Points
- :: Operator — PostgreSQL shorthand:
'42'::int,price::numeric(10,2). - CAST() — Standard SQL:
CAST('42' AS INTEGER). - Implicit Casts — PostgreSQL sometimes casts automatically (e.g., int to bigint).
- Errors — Invalid casts raise errors:
'abc'::intfails. - Common Casts — TEXT to INT, INT to NUMERIC, VARCHAR to DATE, FLOAT to NUMERIC for precision.
Guided Practice
Solve the challenge below. Use hints when stuck and check your answer for instant feedback.
Practice challengeGuided learning mode
Type Casting (CAST / ::) Challenge
Write a query that solve this task: convert a string to an integer.
Expected result
Returns the integer 42.
Hidden checks
- Returned rows and values
- Output columns and result shape
- Final database state after the query runs
Lesson guidance
What is Type Casting?
Initializing database...Each run starts from fresh sample data.
More Examples
Numeric precision with ::
Round a float to 2 decimal places.
Initializing database...Each run starts from fresh sample data.
Integer division fix
Avoid integer division by casting to numeric.
Initializing database...Each run starts from fresh sample data.
Frequently Asked Questions
What is the difference between CAST and ::?
They do the same thing. CAST is SQL standard; :: is PostgreSQL shorthand. Use :: for brevity and CAST for portability.
What happens if a cast fails?
PostgreSQL raises an error. You can handle this with a CASE or by ensuring data is clean before casting.