ClickHouse DB::Exception: Value is out of range of data type

The "DB::Exception: Value is out of range of data type" error occurs when a value exceeds the valid range of the target data type. For instance, trying to store 300 in a UInt8 column (which holds 0–255) or -1 in a UInt32 column triggers this error. ClickHouse enforces strict range checking to prevent silent data truncation or wrapping.

Impact

The INSERT, CAST, or query that produced the out-of-range value fails. In bulk inserts, this can reject the entire batch. The strictness is intentional — ClickHouse won't silently corrupt your data by truncating values, but it means you need to handle range validation before data reaches the database.

Common Causes

1. Value too large for the column type — inserting 100000 into a UInt16 column (max 65535).
2. Negative values in unsigned columns — inserting -1 into UInt32 or UInt64.
3. Date out of range — ClickHouse Date supports 1970-01-01 to 2149-06-06; Date32 extends this range.
4. Integer overflow in expressions — arithmetic that produces a result outside the type's range, e.g., toUInt8(200 + 100).
5. Casting between incompatible ranges — CAST(1000 AS UInt8) or CAST(-5 AS UInt32).
6. Epoch timestamp overflow — converting a millisecond timestamp with toDateTime() when it expects seconds.

Troubleshooting and Resolution Steps

1. Check the target type's range. Common ranges:

   Type	Min	Max
   UInt8	0	255
   UInt16	0	65,535
   UInt32	0	4,294,967,295
   Int8	-128	127
   Int16	-32,768	32,767
   Int32	-2,147,483,648	2,147,483,647
   Date	1970-01-01	2149-06-06

2. Widen the column type if the data legitimately needs a larger range:

   ALTER TABLE your_table MODIFY COLUMN col_name UInt32;  -- was UInt8
   

3. Use a signed type if negative values are needed:

   ALTER TABLE your_table MODIFY COLUMN col_name Int32;  -- was UInt32
   

4. Use OrZero/OrNull conversion functions for safe casting:

   -- Returns 0 instead of throwing an error
   SELECT toUInt8OrZero(toString(300));
   
   -- Returns NULL instead of throwing an error
   SELECT toUInt8OrNull(toString(300));
   

5. Clamp values before insertion:

   INSERT INTO your_table
   SELECT
       least(greatest(value, 0), 255) AS clamped_uint8_col
   FROM source_table;
   

6. Fix date/time conversions. If you're dealing with millisecond timestamps:

   -- Wrong: milliseconds interpreted as seconds
   SELECT toDateTime(1679000000000);
   
   -- Correct: divide by 1000 first
   SELECT toDateTime(intDiv(1679000000000, 1000));
   
   -- Or use DateTime64 for sub-second precision
   SELECT toDateTime64(1679000000000 / 1000, 3);
   

Best Practices

• Choose the smallest type that comfortably fits your data range, but leave room for growth. When in doubt, go one size up.
• Use UInt types only when you're certain values will never be negative.
• Use toTypeOrZero and toTypeOrNull variants for user-facing data or data from untrusted sources.
• Validate data ranges in your ETL pipeline before inserting into ClickHouse.
• For timestamps, always confirm whether your source uses seconds, milliseconds, or microseconds.

Frequently Asked Questions

Q: Why doesn't ClickHouse just truncate the value like some databases do?
A: Silent truncation can lead to data corruption that's extremely hard to detect later. ClickHouse's philosophy is to fail loudly so you know immediately that something is wrong and can fix it at the source.

Q: Can I disable range checking?
A: Not directly. ClickHouse always enforces type ranges. You can use the OrZero or OrNull function variants to handle out-of-range values gracefully in your queries, or widen the column type.

Q: What happens with Float overflow?
A: Float32 and Float64 follow IEEE 754 semantics. Values that overflow become infinity (inf) rather than triggering the VALUE_IS_OUT_OF_RANGE_OF_DATA_TYPE error. This error is specific to integer and date types.

Q: I'm inserting dates before 1970. What should I use?
A: Use Date32 which supports dates from 1900-01-01 to 2299-12-31, or DateTime64 for timestamps with sub-second precision and a wider date range.