How to CAST in ParadeDB

Galaxy Glossary

How do I convert data types with CAST in ParadeDB?

CAST converts a value from one data type to another in ParadeDB, a PostgreSQL-compatible database.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

What does CAST do in ParadeDB?

CAST changes the data type of a value at query time—turning text into integers, timestamps into dates, or JSON into text—so functions and comparisons work correctly.

When should I use CAST?

Use CAST when the column type differs from the function’s expected type, when comparing mixed-type columns, or when preparing data for inserts into stricter typed tables.

What is the exact syntax?

Use the standard CAST(expr AS target_type) or the shorthand expr::target_type.ParadeDB supports both.

How do I CAST text to integer in an ecommerce query?

Convert a text column storing numeric strings to integer before arithmetic:
SELECT CAST(total_amount AS numeric) * 1.05 AS total_with_tax FROM Orders;

Can I CAST JSON fields to text?

Yes. Extract a JSON value, then CAST:
SELECT (order_data->>'promo_code')::text FROM Orders;

How to avoid precision loss?

CAST to the smallest type that fully fits the source data.For money values, CAST to numeric with scale, not integer.

Best practices for CAST

Keep CASTs close to the data source, prefer explicit CAST over implicit, and create views with pre-cast columns to simplify client queries.

Why How to CAST in ParadeDB is important

How to CAST in ParadeDB Example Usage


-- Calculate revenue per product, casting quantities to numeric for division
SELECT p.name,
       SUM(oi.quantity::numeric * p.price) AS revenue
FROM OrderItems oi
JOIN Products   p ON p.id = oi.product_id
GROUP BY p.name;

How to CAST in ParadeDB Syntax


-- Standard form
CAST ( expression AS target_type )

-- Shorthand operator
expression::target_type

-- Example conversions
CAST('2024-04-01' AS date)
'123.45'::numeric(10,2)

-- From ecommerce context
SELECT CAST(total_amount AS numeric(10,2))
FROM Orders
WHERE id = 42;

Common Mistakes

Relying on implicit casting: ParadeDB may pick an unintended type, causing slow plans or errors. Fix by writing explicit CAST or ::.
Casting to an undersized type: Converting numeric(10,2) to integer truncates cents. Always CAST to a type that preserves needed precision.