How to CAST in PostgreSQL

Galaxy Glossary

How do you safely convert one data type to another in PostgreSQL using CAST?

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

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

How to CAST in PostgreSQL

CAST quickly converts one data type to another, allowing accurate comparisons, calculations, and storage in PostgreSQL.

What is the basic CAST syntax?

Use either the functional form CAST(expression AS target_type) or the shorthand expression::target_type. Both return the value in the new data type.

How do I cast text to numbers?

Wrap the text literal or column in CAST. Example: SELECT CAST('42' AS integer);. Shorthand: SELECT '42'::int;.Ensure the string is numeric to avoid runtime errors.

How do I cast timestamps to dates?

Strip the time portion by casting: SELECT NOW()::date;. PostgreSQL drops the hour, minute, and second, returning only the calendar date.

Can I cast during table creation or inserts?

Yes. Example: INSERT INTO sales (amount) VALUES (CAST($1 AS numeric));.This keeps client-side strings from polluting numeric columns.

What are best practices for safe casting?

Validate data before casting, use NULLIF to handle blanks, and prefer explicit CAST in mission-critical code for clarity and future maintainability.

Example: safe numeric cast

SELECT CAST(NULLIF(raw_text, '') AS numeric) FROM staging; avoids errors when raw_text is empty.

How do I cast arrays and JSON?

Use the same syntax: SELECT '{1,2,3}'::int[]; or SELECT CAST('{"a":1}' AS jsonb);.Ensure the literal follows array or JSON rules.

Is CAST faster than shorthand (::)?

Performance is identical; both compile to the same plan. Choose the style that improves readability for your team.

Summary tips

Cast explicitly in migrations, check input validity, specify length when casting to varchar, and monitor for overflow or precision loss.

Why How to CAST in PostgreSQL is important

How to CAST in PostgreSQL Example Usage


-- Convert string to integer and timestamp to date in one query
SELECT CAST('123' AS integer)  AS user_id,
       CAST('2024-06-18 09:00' AS date) AS signup_date;

How to CAST in PostgreSQL Syntax


CAST ( expression AS target_type )
expression::target_type

Parameters:
  expression     The value or column you want to convert.
  target_type    Any valid PostgreSQL data type (optionally with length, precision, or array notation).

Common Mistakes

Casting invalid input (e.g., 'abc'::int) raises an error. Fix by validating with a regex, using NULLIF, or filtering rows before casting.
Forgetting length when casting to varchar can silently truncate data. Always specify the desired length: CAST(col AS varchar(50)).

Frequently Asked Questions (FAQs)

Does CAST change the stored data?

No. CAST only affects the returned value. To persist the new type, update the column or use ALTER TABLE.

Is there a performance cost to frequent casting?

Minor. PostgreSQL applies a type conversion function, which is negligible for small datasets but can add up in large joins. Pre-convert columns when possible.