How to CAST in PostgreSQL

How do I CAST values to different data types in Redshift?

CAST converts a value from one data type to another using standard or Redshift-specific syntax.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What does CAST do in PostgreSQL?

CAST converts a literal, column, or expression to a target data type so functions, joins, and comparisons work without type errors.

When should I use CAST in Redshift queries?

Use CAST when implicit conversion fails, when you need exact precision (e.g., money → numeric), or when joining columns stored as different types.

How do I write CAST syntax correctly?

Place CAST(expr AS target_type) in your SELECT, WHERE, GROUP BY, or JOIN clauses, or use the shorthand expr::target_type.

Can I CAST between any data types?

You can cast between compatible types like varchar ⇆ int, numeric ⇆ float, timestamp ⇆ date. Incompatible casts return an error.

Why does my CAST fail with invalid input?

Redshift validates each row; non-numeric text such as ‘ABC’ triggers an error when CASTing to int. Clean data or use NULLIF to skip bad rows.

Example: Convert order totals for currency math

To calculate 10% VAT on total_amount stored as varchar, CAST it to numeric, then multiply.

SELECT id, total_amount::numeric * 0.10 AS vat FROM Orders;

Best practice: combine CAST with COALESCE

Wrap CAST in COALESCE to replace NULL with a default value, ensuring downstream calculations stay robust.

How to avoid performance hits?

Cast columns in staging tables rather than on the fly in large analytic queries. This lets Redshift scan pre-typed data.

What are common mistakes with CAST?

Mistake 1: Casting malformed data. Fix by using REGEXP or TRY_CAST. Mistake 2: Forgetting precision (numeric(10,2)). Fix by always specifying scale.

TRY_CAST alternative in Redshift?

TRY_CAST(expr AS type) returns NULL instead of error on bad rows, perfect for incremental loads.

Why How to CAST in PostgreSQL is important

How to CAST in PostgreSQL Example Usage


-- Join Orders to Customers when customer_id is stored as text
SELECT o.id,
       c.name,
       o.total_amount
FROM   Orders o
JOIN   Customers c
       ON c.id = CAST(o.customer_id AS INTEGER);

How to CAST in PostgreSQL Syntax


CAST ( expression AS target_type )
expression::target_type

-- Supported target_type examples
BOOLEAN | SMALLINT | INTEGER | BIGINT | DECIMAL(p,s) | REAL | DOUBLE PRECISION |
VARCHAR(n) | DATE | TIMESTAMP

-- Ecommerce demo
SELECT CAST(order_date AS DATE)
FROM   Orders;

SELECT total_amount::numeric(12,2)
FROM   Orders;

Common Mistakes

Casting malformed strings (e.g., '12A') to numeric causes the query to fail; cleanse data or use TRY_CAST to return NULL instead.
Omitting precision/scale when casting to NUMERIC leads to unexpected rounding; always specify NUMERIC(precision, scale).