Type casting with :: operator in PostgreSQL

Introduction

Type casting changes a value from one type to another so the database can understand or use it correctly.

When you want to convert a number stored as text into a real number to do math.

When you need to change a date stored as text into a date type to compare dates.

When you want to convert an integer to text to combine it with other text.

When you want to make sure a value matches the expected type in a query or function.

Syntax

PostgreSQL

value::target_type

The :: operator is a simple way to convert types in PostgreSQL.

Make sure the value can be converted to the target type, or you will get an error.

Examples

Converts the text '123' into the number 123.

PostgreSQL

'123'::integer

Converts the number 456 into the text '456'.

PostgreSQL

456::text

Converts the text '2024-06-01' into a date type.

PostgreSQL

'2024-06-01'::date

Sample Program

This query converts '100' from text to integer to add 50, and converts 200 from integer to text to join with ' apples'.

PostgreSQL

SELECT '100'::integer + 50 AS result, 200::text || ' apples' AS description;

Important Notes

If the value cannot be converted, PostgreSQL will return an error.

You can also use the CAST(value AS target_type) syntax as an alternative.

Type casting helps avoid mistakes when mixing different data types in queries.

Summary

Type casting changes data from one type to another using :: operator.

It is useful for math, text operations, and date comparisons.

Always check that the value can be converted to avoid errors.