How to CAST in PostgreSQL

How do I use the CAST function to convert data types in PostgreSQL?

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

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What does CAST do in PostgreSQL?

CAST forces PostgreSQL to treat a value as a different data type, enabling arithmetic, comparison, and formatting operations that require matching types.

Why choose CAST over the :: operator?

Both work, but CAST is SQL-standard and easier to parameterize in ORMs or when migrating from SQL Server, making scripts more portable.

What is the exact CAST syntax?

Use CAST(expression AS target_type) or the shorthand expression::target_type.You can also create custom casts for user-defined types.

How do I convert text to DATE safely?

Pass a format mask to to_date() first, then CAST: CAST(to_date('2024-06-12','YYYY-MM-DD') AS DATE).This avoids runtime errors from malformed strings.

Example: Calculate revenue per month

The query below casts order_date to DATE, truncates to month, and sums totals.

SELECT date_trunc('month', CAST(order_date AS DATE)) AS month, SUM(total_amount) AS monthly_revenue FROM Orders GROUP BY 1 ORDER BY 1;

Best practices for using CAST

Always validate input formats, prefer explicit CAST for readability, and index on the converted column if the query runs frequently.

What mistakes should I avoid?

Avoid implicit casting that hides performance hits and never CAST incompatible types without checking for data loss.

Why How to CAST in PostgreSQL is important

How to CAST in PostgreSQL Example Usage


-- Combine product price (NUMERIC) and quantity (INT) to get line totals
SELECT oi.order_id,
       p.name,
       p.price,
       oi.quantity,
       p.price * CAST(oi.quantity AS NUMERIC(10,2)) AS line_total
FROM   OrderItems oi
JOIN   Products    p ON p.id = oi.product_id;

How to CAST in PostgreSQL Syntax


CAST(expression AS target_type)

-- Common target_type values
INTEGER | BIGINT | NUMERIC(p,s) | DECIMAL(p,s)
DATE | TIMESTAMP | TIME
BOOLEAN | TEXT | VARCHAR(n)

-- Ecommerce examples
-- 1. Convert Orders.total_amount from TEXT to NUMERIC for math
SELECT order_id, CAST(total_amount AS NUMERIC(10,2)) AS amount
FROM   Orders;

-- 2. Join Customers & Orders when IDs are stored differently
SELECT c.name, o.id
FROM   Customers c
JOIN   Orders o ON c.id = CAST(o.customer_id AS INTEGER);

Common Mistakes

Relying on implicit casting: PostgreSQL may choose a sub-optimal plan or fail when types are ambiguous. Fix by writing an explicit CAST for all critical expressions.
Casting without size/precision: Converting TEXT to NUMERIC without specifying (p,s) can default to unlimited precision and slow queries. Provide NUMERIC(10,2) to control storage.

Frequently Asked Questions (FAQs)

Can I cast NULL values?

Yes. CAST(NULL AS INTEGER) returns an INTEGER NULL, preserving type information for functions and UNION compatibility.

Does CAST affect indexes?

Only if you CAST the indexed column in the query, which can prevent index usage. Create a functional index on the casted expression to keep performance.