---
title: CANNOT_CONVERT_TYPE ClickHouse error
meta:
  description: Learn how to fix the CANNOT_CONVERT_TYPE error in ClickHouse and Tinybird. Understand what causes it and see examples of how to resolve it
---

# CANNOT_CONVERT_TYPE ClickHouse error

{% callout %}
This error occurs when ClickHouse cannot automatically convert between incompatible data types. It's common when comparing different types, using functions with wrong arguments, or inserting data with type mismatches.
{% /callout %}

The `CANNOT_CONVERT_TYPE` error in ClickHouse (and Tinybird) happens when the system cannot automatically convert between incompatible data types. This typically occurs when comparing different types, using functions with arguments of the wrong type, or when inserting data that doesn't match the expected column types.

## What causes this error

You'll typically see it when:

* Comparing different data types in WHERE clauses
* Using functions with incompatible argument types
* Inserting data with type mismatches
* Performing operations between incompatible types
* Using wrong data types in JOIN conditions
* Passing incorrect types to aggregate functions
* Type casting operations fail
* Array operations with mixed types

{% callout type="tip" %}
ClickHouse is strict about type conversions. Use explicit CAST functions when you need to convert between types.
{% /callout %}

## Example errors

```sql {% title="Fails: comparing different types" %}
SELECT * FROM events
WHERE user_id = '123'  -- user_id is UInt64, '123' is String
-- Error: Cannot convert type String to UInt64
```

```sql {% title="Fails: function with wrong argument type" %}
SELECT toDate('2024-01-01') as date
WHERE date > 123  -- date is Date, 123 is UInt64
-- Error: Cannot convert type UInt64 to Date
```

```sql {% title="Fails: type mismatch in JOIN" %}
SELECT * FROM users u
JOIN events e ON u.id = e.user_id
WHERE u.id = 'user_123'  -- u.id is UInt64, 'user_123' is String
-- Error: Cannot convert type String to UInt64
```

```sql {% title="Fails: array with mixed types" %}
SELECT [1, '2', 3.0] as mixed_array
-- Error: Cannot convert type String to UInt64
```

## How to fix it

### Use explicit type casting

Convert types explicitly using CAST functions:

```sql {% title="Fix: explicit type casting" %}
SELECT * FROM events
WHERE user_id = CAST('123' AS UInt64)
```

### Fix data type mismatches

Ensure data types match your schema:

```sql {% title="Fix: correct data types" %}
SELECT * FROM events
WHERE user_id = 123  -- Use integer instead of string
```

### Use appropriate type conversion functions

Use ClickHouse's built-in conversion functions:

```sql {% title="Fix: use conversion functions" %}
SELECT toDate('2024-01-01') as date
WHERE date > toDate('2023-01-01')  -- Convert to Date type
```

### Fix array type consistency

Ensure arrays contain consistent types:

```sql {% title="Fix: consistent array types" %}
SELECT [1, 2, 3] as int_array  -- All integers
SELECT ['a', 'b', 'c'] as string_array  -- All strings
```

## Common patterns and solutions

### String to numeric conversion

Convert strings to appropriate numeric types:

```sql {% title="String to numeric conversion" %}
-- Convert string to integer
SELECT * FROM events
WHERE user_id = toUInt64('123')

-- Convert string to float
SELECT * FROM metrics
WHERE value = toFloat64('123.45')

-- Convert string to decimal
SELECT * FROM transactions
WHERE amount = toDecimal64('123.45', 2)
```

### Date and time conversions

Handle date and time type conversions:

```sql {% title="Date and time conversions" %}
-- Convert string to date
SELECT * FROM events
WHERE event_date = toDate('2024-01-01')

-- Convert string to datetime
SELECT * FROM events
WHERE timestamp = toDateTime('2024-01-01 10:00:00')

-- Convert timestamp to date
SELECT * FROM events
WHERE toDate(timestamp) = toDate('2024-01-01')
```

### Array type handling

Ensure array type consistency:

```sql {% title="Array type handling" %}
-- Convert array elements to consistent types
SELECT arrayMap(x -> toUInt64(x), ['1', '2', '3']) as int_array

-- Filter arrays by type
SELECT arrayFilter(x -> isUInt64(x), [1, '2', 3]) as filtered_array

-- Convert mixed arrays to strings
SELECT arrayMap(x -> toString(x), [1, '2', 3.0]) as string_array
```

### Function argument type conversion

Convert function arguments to expected types:

```sql {% title="Function argument conversion" %}
-- Convert string to date for date functions
SELECT toYYYYMM(toDate('2024-01-01')) as year_month

-- Convert numeric to string for string functions
SELECT length(toString(12345)) as string_length

-- Convert string to array for array functions
SELECT has(arrayMap(x -> toUInt64(x), ['1', '2', '3']), 2) as has_value
```

## Tinybird-specific notes

In Tinybird, CANNOT_CONVERT_TYPE errors often occur when:

* Data Sources have type mismatches
* Pipe transformations use wrong data types
* API endpoints receive data with unexpected types
* Materialized Views have type conflicts

To debug in Tinybird:

1. Check Data Source schema for type definitions
2. Verify Pipe transformation data types
3. Ensure API input validation matches expected types
4. Review Materialized View type consistency

{% callout type="tip" %}
In Tinybird, use the Data Source preview to identify type mismatches before they cause errors.
{% /callout %}

## Best practices

### Type consistency

* Always use consistent data types in your schema
* Validate data types before insertion
* Use explicit type casting when needed
* Document expected data types

### Data validation

* Implement input validation in your application
* Check data types before processing
* Use appropriate default values for missing data
* Handle type conversion errors gracefully

### Schema design

* Choose appropriate data types for your use case
* Consider storage and performance implications
* Use nullable types when appropriate
* Plan for future data type changes

## Configuration options

### Type conversion settings

```sql {% title="Type conversion settings" %}
-- Check type conversion settings
SELECT
    name,
    value,
    description
FROM system.settings
WHERE name LIKE '%type%' OR name LIKE '%convert%'
```

### Strict type checking

```sql {% title="Strict type checking" %}
-- Enable strict type checking
SET strict_type_checking = 1;

-- Disable automatic type conversion
SET allow_experimental_object_type = 0;
```

### Type inference

```sql {% title="Type inference settings" %}
-- Allow type inference for literals
SET allow_experimental_infer_schema = 1;

-- Enable automatic type detection
SET allow_experimental_auto_type_inference = 1;
```

## Alternative solutions

### Use type-safe functions

Use functions that handle type conversion automatically:

```sql {% title="Type-safe functions" %}
-- Use toTypeOrNull for safe conversion
SELECT toTypeOrNull('123', 'UInt64') as safe_int

-- Use toTypeOrZero for default values
SELECT toTypeOrZero('invalid', 'UInt64') as default_int

-- Use toTypeOrDefault with custom default
SELECT toTypeOrDefault('invalid', 'UInt64', 0) as custom_default
```

### Implement type validation

Add type validation in your application:

```sql {% title="Type validation" %}
-- In your application, validate types before querying
-- Example pseudo-code:
--
-- def validate_user_id(user_id):
--     try:
--         if isinstance(user_id, str):
--             return int(user_id)
--         elif isinstance(user_id, int):
--             return user_id
--         else:
--             raise ValueError(f"Invalid user_id type: {type(user_id)}")
--     except ValueError:
--         raise ValueError(f"Invalid user_id value: {user_id}")
```

### Use dynamic type handling

Handle types dynamically in your queries:

```sql {% title="Dynamic type handling" %}
-- Use CASE statements for type-dependent logic
SELECT
    CASE
        WHEN isString(user_id) THEN toUInt64(user_id)
        WHEN isUInt64(user_id) THEN user_id
        ELSE 0
    END as safe_user_id
FROM events
```

## Monitoring and prevention

### Type error tracking

```sql {% title="Type error monitoring" %}
-- Monitor type conversion errors
-- Example pseudo-code:
--
-- def track_type_error(operation, expected_type, actual_type, value):
--     logger.error(f"Type conversion failed: {operation}")
--     logger.error(f"Expected: {expected_type}, Got: {actual_type}")
--     logger.error(f"Value: {value}")
--
--     # Track error metrics
--     increment_counter('type_conversion_errors', {
--         'operation': operation,
--         'expected_type': expected_type,
--         'actual_type': actual_type
--     })
```

### Schema validation

```sql {% title="Schema validation" %}
-- Validate table schema for type consistency
SELECT
    table,
    name,
    type,
    default_expression
FROM system.columns
WHERE database = currentDatabase()
  AND table = 'your_table_name'
ORDER BY position
```

### Data quality checks

```sql {% title="Data quality monitoring" %}
-- Check for type inconsistencies in data
SELECT
    column_name,
    COUNT(*) as total_rows,
    COUNT(CASE WHEN value IS NULL THEN 1 END) as null_count,
    COUNT(CASE WHEN value = '' THEN 1 END) as empty_string_count
FROM your_table
GROUP BY column_name
```

## See also

* [Data Types](/sql-reference/data-types)
* [Type Conversion Functions](/sql-reference/functions/type-conversion-functions)
* [Data Validation](/forward/work-with-data/optimize)