How to CAST in PostgreSQL

Galaxy Glossary

How do I cast data types in PostgreSQL?

CAST converts a value from one data type to another in PostgreSQL.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

What does the CAST function do?

CAST changes the data type of a value or column, letting you treat it as another compatible type for calculations, comparisons, or storage.

When should I use CAST?

Use CAST when joining columns of different types, formatting output, inserting data that needs conversion, or enforcing precision on arithmetic.

How do I write the syntax?

In PostgreSQL you can use either the CAST(expr AS type) function or the shorthand expr::type operator. Both are interchangeable.

Which data types can I convert?

Common conversions include text ↔ integer, numeric ↔ text, timestamp ↔ date, and UUID ↔ text. Conversions that lose precision may need an explicit USING clause.

Does CAST affect performance?

Simple casts are cheap, but casting on indexed columns can prevent index use. Pre-cast data or create functional indexes to keep queries fast.

Practical examples

Convert order totals from numeric to text for JSON export:
SELECT id, total_amount::text AS total_str FROM Orders;

Join integer customer IDs with text input:
SELECT * FROM Customers c JOIN Orders o ON c.id = CAST($1 AS integer);

Best practices

Cast once, not repeatedly in subqueries. Prefer explicit casts over implicit to avoid surprises. Validate that the target type can hold every source value.

What are common mistakes?

See below for two frequent errors and their fixes.

Why How to CAST in PostgreSQL is important

How to CAST in PostgreSQL Example Usage


-- Calculate average order total per month as text for reporting
SELECT DATE_TRUNC('month', order_date) AS month,
       CAST(AVG(total_amount) AS numeric(10,2))::text AS avg_total
FROM Orders
GROUP BY 1
ORDER BY 1;

How to CAST in PostgreSQL Syntax


-- Function style
CAST ( expression AS target_type )

-- Operator style
expression::target_type

-- Options
• expression: literal, column, or subquery result
• target_type: any PostgreSQL data type

Example conversions in ecommerce schema:
1. Numeric → Text
   SELECT total_amount::text FROM Orders;
2. Text → Integer
   SELECT CAST('123' AS integer);
3. Timestamp → Date
   SELECT order_date::date FROM Orders;

Common Mistakes

Casting text to integer without validating content. If non-numeric characters exist, PostgreSQL throws an error. Fix: use <code>WHERE text_col ~ '^\\d+$'</code> or <code>NULLIF</code> to filter/clean data before casting.
Casting indexed columns in WHERE clauses, which disables index scans and slows queries. Fix: create a functional index on the casted expression or cast literals to the column type instead.