How to CAST in ClickHouse

Galaxy Glossary

How do I use CAST to change data types in ClickHouse?

CAST converts a value or column to a different data type in ClickHouse, enabling accurate comparisons, arithmetic, and joins.

Sign up for the latest in SQL knowledge from the Galaxy Team!

Description

What does CAST do in ClickHouse?

CAST converts an expression to the specified data type so functions, comparisons, and joins work without type errors.

How do I write a CAST?

Use either CAST(expr AS type) or functional cast(expr,'type'). Both forms accept the same data-type keywords.

Why pick one style over the other?

Functional style is easier to build in dynamic SQL strings, while ANSI style improves readability in static queries.

How to cast text dates to DateTime?

Convert an Orders.order_date string to DateTime for date math:

SELECT CAST(order_date AS DateTime) AS order_ts FROM Orders;

How to cast numbers to Decimal without losing cents?

Specify precision and scale explicitly:

SELECT CAST(total_amount AS Decimal(18,2)) AS amount FROM Orders;

Can I cast to Nullable?

Yes—wrap the target type with Nullable():

SELECT CAST(stock AS Nullable(Int32)) AS stock_nullable FROM Products;

How to use cast() inside expressions?

Combine with arithmetic to avoid implicit casts:

SELECT customer_id, total_amount / cast(quantity,'Float64') AS unit_price FROM Orders o JOIN OrderItems i ON i.order_id = o.id;

Best practices for CAST

Cast early in CTEs, validate source data with isValidUTF8 or regex, and always document precision when using Decimal.

Why How to CAST in ClickHouse is important

How to CAST in ClickHouse Example Usage


/* Calculate average order value per customer by casting */
WITH order_totals AS (
    SELECT customer_id,
           cast(total_amount,'Float64') AS amount
    FROM Orders
)
SELECT c.name,
       avg(amount) AS avg_order_value
FROM Customers c
JOIN order_totals o ON o.customer_id = c.id
GROUP BY c.name
ORDER BY avg_order_value DESC;

How to CAST in ClickHouse Syntax


CAST(expression AS target_type)
cast(expression, 'target_type')

-- Supported target_type examples
Int8 | Int16 | Int32 | Int64
UInt8 | UInt16 | UInt32 | UInt64
Float32 | Float64
Decimal(precision, scale)
String | FixedString(N)
Date | DateTime | DateTime64
Nullable(type)

-- E-commerce context
SELECT CAST(order_date AS DateTime)   FROM Orders;
SELECT cast(total_amount,'Decimal(18,2)') FROM Orders;

Common Mistakes

Omitting precision and scale when casting to Decimal. Without explicit values, ClickHouse may pick defaults that round monetary values. Fix by specifying both, e.g., Decimal(18,2).
Casting incompatible strings (e.g., 'abc') to numeric types, which yields zero. Always validate input or filter invalid rows before casting.

Frequently Asked Questions (FAQs)

Is CAST expensive in ClickHouse?

CAST is vectorized and generally fast, but excessive casting in large queries can add noticeable CPU overhead. Cast once in a subquery or materialized view when possible.

Can I cast arrays or nested data?

Yes. Use CAST(array_column AS Array(Int32)) or cast(nested_col,'Tuple(name String, qty Int32)') to convert complex types.

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.

Trusted by top engineers on high-velocity teams

Assort Health

Curri

Rubie