How to CAST in PostgreSQL

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

Why do you need CAST?

CAST lets you convert numbers, text, dates, JSON, and more to the exact data type your query or application expects, preventing type-mismatch errors and enabling index use.

What is the basic CAST syntax?

PostgreSQL offers two interchangeable forms: CAST(expression AS target_type) and the shorthand expression::target_type. Both return a new value without altering the source column.

How do I cast numbers to text?

Wrap the numeric column or literal and specify TEXT:
SELECT total_amount::TEXT AS amount_str FROM Orders; This is handy when concatenating values for reports.

How do I cast text to timestamp?

Provide a parsable string:
SELECT CAST('2024-06-15 12:34' AS TIMESTAMP); Invalid formats trigger Invalid input syntax for type timestamp.

Can I cast inside SELECT with other columns?

Yes. Example converting cents to dollars:
SELECT id, total_amount/100.0 AS dollars, (total_amount/100.0)::NUMERIC(10,2) AS dollars_exactFROM Orders;

Can I cast during JOINs?

Use CAST to align key types:
SELECT *FROM Orders oJOIN Customers c ON o.customer_id::TEXT = c.id::TEXT; Converting both sides avoids planner warnings.

What is the :: shorthand?

:: is purely syntactic sugar around CAST(). Use whichever style improves readability in your team.

Best practices for CAST

• Prefer explicit casts in production code.
• Match precision/scale when casting numerics.
• Avoid casting away indexes; cast the literal instead of the column.
• Validate text formats before casting to dates or JSON.

Common mistakes and fixes

Using wrong date format: CAST('06/15/2024' AS DATE) fails if datestyle is ISO. Fix with TO_DATE('06/15/2024','MM/DD/YYYY').

Casting NULL without type: CAST(NULL AS TEXT) or NULL::TEXT required; bare NULL keeps an unknown type and breaks unions.

Why How to CAST in PostgreSQL is important

How to CAST in PostgreSQL Example Usage


-- Show order totals as formatted dollars string
SELECT o.id,
       ('$' || (o.total_amount/100.0)::NUMERIC(10,2)) AS total_display
FROM Orders o
WHERE o.order_date >= CURRENT_DATE - INTERVAL '30 days';

How to CAST in PostgreSQL Syntax


CAST(expression AS target_type);

-- Shorthand
expression::target_type

-- Ecommerce examples
-- 1. Convert integer cents to money
SELECT total_amount::NUMERIC(12,2)
FROM Orders;

-- 2. String to timestamp for imported CSV data
SELECT CAST(order_date AS TIMESTAMP)
FROM Orders_Staging;

Common Mistakes

Mistake: Casting the indexed column instead of the literal (e.g., customer_id::TEXT = '42'). This prevents index use. Fix by casting the literal: customer_id = '42'::INTEGER.
Mistake: Implicitly trusting text to date conversions. Incorrect formats raise runtime errors. Always validate or use TO_DATE with an explicit mask.