Type Casting

Objectives

By the end of this lesson, you should be able to:

• Convert a value from one data type to another with CAST() and ::
• Explain when a cast is needed
• Predict when a cast fails

💡 Why this matters: Not every value arrives in the type a query needs. Text that looks like a number, a number that needs to be displayed as text, these all need an explicit conversion, called a cast, before they’ll work the way the query expects.

⚠️ A note on verification: every statement and result in this lesson was run against a real, live PostgreSQL 18 database, using the same employees table as the rest of this module.

Two Ways to Cast

SELECT '42'::INTEGER + 8 AS result;

 result
--------
     50

SELECT CAST('42' AS INTEGER) + 8 AS result;

 result
--------
     50

value::type and CAST(value AS type) do exactly the same thing, converting value to type. :: is a PostgreSQL-specific shorthand, CAST() is the standard SQL syntax that works across most databases. Both are used constantly in real PostgreSQL code, :: is more common for its brevity.

Casting a Number to Text

SELECT 'Salary: ' || salary::TEXT AS label
FROM employees
WHERE first_name = 'Erin';

      label
-------------------
 Salary: 74000.00

The || operator (Module 7, lesson 2) only concatenates text values. salary is NUMERIC, not text, so it needs ::TEXT first, without the cast, PostgreSQL would raise a type error rather than silently guessing what was meant.

Casting Text to a Date

SELECT '2024-01-15'::DATE AS parsed_date;

 parsed_date
-------------
 2024-01-15

A text literal that looks like a date isn’t automatically a DATE value, casting it makes it one, usable in date arithmetic or comparisons against a DATE column.

When a Cast Fails

SELECT 'not a number'::INTEGER;

ERROR:  invalid input syntax for type integer: "not a number"

A cast only succeeds if the value can genuinely be interpreted as the target type. 'not a number' can’t become an INTEGER, so PostgreSQL raises an error rather than guessing or returning NULL. This is the same principle as a CHECK constraint (Module 5) or a NOT NULL violation, PostgreSQL rejects data it can’t validate rather than silently accepting something wrong.

Try It

1. Write a query that casts the text '100' to INTEGER and adds 50 to it, using ::.
2. Write a query that casts every employee’s id (an INTEGER) to TEXT and concatenates it with ' - ' and their first_name.
3. Write a query that casts the text '2025-12-25' to DATE.
4. Predict, then confirm, what happens when casting the text '12.5' to INTEGER rather than NUMERIC.

Recap

• value::type and CAST(value AS type) are equivalent ways to convert a value’s type, :: is PostgreSQL-specific shorthand.
• Casting is often required before combining values of different types, like concatenating a number into text.
• A cast fails with an error if the value can’t genuinely be interpreted as the target type, PostgreSQL never silently guesses.

Next lesson: this module’s exercises, practicing every function together.