Data Type Conversion with CAST() and :: — Examples and Gotchas

Imagine you want to add two numbers, but one of the parameters is stored as a string. Or maybe you want to filter data by year, but your date is stored in full format with time. PostgreSQL might not be too happy with your plan, and you'll have to step in to convert the data to the right type.

Type conversion helps you to:

• Make data processing easier so you can use it in calculations.
• Create more readable and structured data for export or analysis.
• Fix errors related to data type mismatches.

Basics of Data Type Conversion: CAST() and ::

1. CAST() — the big brother of all conversions

The CAST() function is the "official" way to convert values from one data type to another. It works like a universal translator.

Syntax:

CAST(value AS target_data_type)

Example 1: Converting a string to a number.

SELECT CAST('123' AS INTEGER); -- Result: 123

Example 2: Converting a number to a string.

SELECT CAST(123 AS TEXT); -- Result: '123'

Example 3: Converting a date to a string.

SELECT CAST(NOW() AS TEXT); -- Result: '2023-10-25 15:00:00.000000'

2. The :: operator — a short and sweet alternative

If CAST() feels a bit too wordy, PostgreSQL gives you the :: syntax. It's a shortcut for conversion, popular because it's so concise.

Syntax:

value::target_data_type

Example 1: Converting a string to a number.

SELECT '123'::INTEGER; -- Result: 123

Example 2: Converting a number to a string.

SELECT 123::TEXT; -- Result: '123'

Example 3: Converting a date to a string.

SELECT NOW()::TEXT; -- Result: '2023-10-25 15:00:00.000000'

Real-life Examples of Type Conversion

1. Converting numbers and strings

Let's say you have a students table where the student_id column is stored as a string, but you want to compare it to a numeric value. Without conversion, it's not gonna work.

So in your query, you need to explicitly convert the student_id column to a number:

SELECT *
FROM students
WHERE student_id::INTEGER = 101;

Same query using CAST():

SELECT *
FROM students
WHERE CAST(student_id AS INTEGER) = 101;

Converting dates

When you need to extract part of a date or turn it into a string, type conversion comes to the rescue too. For example, you have a courses table with course start dates in the start_date column.

Example: Extract the year as a number.

SELECT start_date::DATE, start_date::TEXT, start_date::TIMESTAMP
FROM courses;

The result will look like this:

Example: Convert a date to a "clean string".

SELECT TO_CHAR(start_date, 'DD-MM-YYYY')

The result will look like this:

Error: When does conversion break?

The data conversion trick doesn't always work. Sometimes PostgreSQL will straight up tell you it "doesn't get" what you're trying to do. Here's a classic example: you try to convert text that looks like a number, but the text has extra characters.

Error example:

SELECT '123abc'::INTEGER;
-- ERROR:  invalid input syntax for type integer: "123abc"

To avoid these problems, make sure your data can actually be converted to the target type ahead of time. For example, you can use regular expressions or extra checks.