UNKNOWN_TYPE ClickHouse error¶

The UNKNOWN_TYPE error in ClickHouse (and Tinybird) happens when you try to use a data type that doesn't exist in ClickHouse or isn't available in your version. This commonly occurs when using types from other database systems or trying to use newer ClickHouse types in older versions.

What causes this error¶

You'll typically see it when:

• Using data types from other database systems (MySQL, PostgreSQL, etc.)
• Misspelling ClickHouse type names
• Using types available in newer ClickHouse versions
• Using custom types that haven't been defined
• Using deprecated or removed types

Example errors¶

CREATE TABLE events (id INT, name VARCHAR(255))

CREATE TABLE events (id UInt32, name Strng)

CREATE TABLE events (id UInt32, data JSONB)

CREATE TABLE events (id uint32, name String)

How to fix it¶

Use ClickHouse data types¶

Replace with equivalent ClickHouse types:

CREATE TABLE events (id UInt32, name String)

Check type spelling¶

Verify the correct type name:

CREATE TABLE events (id UInt32, name String)

Use proper case¶

ClickHouse types are case-sensitive:

CREATE TABLE events (id UInt32, name String)

Check type availability¶

Verify the type exists in your ClickHouse version:

SELECT version()

Common patterns and solutions¶

Basic data types¶

Common ClickHouse data types:

CREATE TABLE events (
    id UInt32,
    name String,
    value Float64,
    active UInt8,
    timestamp DateTime
)

Integer types¶

ClickHouse integer types:

CREATE TABLE events (
    tiny_id UInt8,
    small_id UInt16,
    normal_id UInt32,
    big_id UInt64,
    signed_id Int32
)

String types¶

ClickHouse string types:

CREATE TABLE events (
    name String,
    fixed_name FixedString(10),
    uuid UUID
)

Complex types¶

ClickHouse complex types:

CREATE TABLE events (
    id UInt32,
    tags Array(String),
    metadata Map(String, String),
    nullable_name Nullable(String),
    decimal_value Decimal(10, 2)
)

Date and time types¶

ClickHouse date/time types:

CREATE TABLE events (
    date_col Date,
    datetime_col DateTime,
    timestamp_col DateTime64(3)
)

Type mapping from other databases¶

MySQL to ClickHouse¶

PostgreSQL to ClickHouse¶

Advanced solutions¶

Using complex types¶

ClickHouse complex types:

CREATE TABLE events (
    id UInt32,
    tags Array(String),
    metadata Map(String, String),
    coordinates Tuple(Float64, Float64)
)

Using Nullable types¶

For columns that can be NULL:

CREATE TABLE events (
    id UInt32,
    name Nullable(String),
    value Nullable(Float64)
)

Using LowCardinality for optimization¶

For columns with repeated values:

CREATE TABLE events (
    id UInt32,
    event_type LowCardinality(String),
    user_id UInt32
)

Tinybird-specific notes¶

In Tinybird, this error often occurs when:

• Using Data Sources with unsupported types
• Creating Pipes with incompatible type definitions
• Using Materialized Views with wrong types
• Working with imported schemas from other databases

To debug in Tinybird:

1. Check Tinybird's supported ClickHouse types
2. Use the Data Source schema editor to verify types
3. Test type definitions in the Query Builder
4. Use Schema Hints to enforce proper types

Best practices¶

Type selection¶

• Use appropriate types for your data
• Consider storage and performance implications
• Use Nullable types when needed
• Use LowCardinality for repeated string values

Type validation¶

• Test type definitions with sample data
• Verify type compatibility across operations
• Use type conversion functions when needed
• Document type choices and reasons

Performance considerations¶

• Use smallest appropriate integer types
• Consider using LowCardinality for string columns
• Use appropriate precision for decimal types
• Index columns with appropriate types

See also¶

• ClickHouse Data Types
• Working with Data Sources
• Common function errors