ClickHouse DB::Exception: Unknown data type

The "DB::Exception: Unknown type" error means ClickHouse does not recognize the data type name you used in a column definition, CAST expression, or function call. This happens when you misspell a type, use a type from another database system that ClickHouse doesn't support, or try to use a type that requires a specific ClickHouse version or feature flag.

Impact

The DDL statement or query fails immediately. You cannot create the table, alter the column, or execute the cast until the type name is corrected. This is a straightforward error to fix once you identify the right type name.

Common Causes

1. Misspelled type name — Uint32 instead of UInt32, or Varchar instead of String.
2. Using types from other databases — BIGINT, VARCHAR, TEXT, BOOLEAN (though some of these have been added as aliases in recent versions).
3. Missing parametric arguments — writing Decimal without precision and scale: Decimal(18, 4).
4. Feature-gated types — some types like Object('json') or experimental types require specific settings to be enabled.
5. Version mismatch — using a type introduced in a newer ClickHouse version than what's running.
6. Case sensitivity — ClickHouse type names are case-sensitive: UInt32 works but uint32 does not.

Troubleshooting and Resolution Steps

1. Check the spelling and casing. ClickHouse types use specific capitalization. Common correct names:

   • UInt8, UInt16, UInt32, UInt64
   • Int8, Int16, Int32, Int64
   • Float32, Float64
   • String, FixedString(N)
   • Date, DateTime, DateTime64
   • Decimal(P, S), Decimal32(S), Decimal64(S), Decimal128(S)
   • UUID, IPv4, IPv6
   • Array(T), Tuple(T1, T2, ...), Map(K, V)
   • Nullable(T), LowCardinality(T)

2. Map types from other databases:

   Other DB	ClickHouse
   BIGINT	Int64
   VARCHAR	String
   TEXT	String
   BOOLEAN	Bool (UInt8)
   DOUBLE	Float64
   TIMESTAMP	DateTime

3. Add required parameters for parametric types:

   -- Wrong
   CREATE TABLE t (val Decimal) ENGINE = MergeTree() ORDER BY tuple();
   
   -- Correct
   CREATE TABLE t (val Decimal(18, 4)) ENGINE = MergeTree() ORDER BY tuple();
   

4. Enable experimental types if needed:

   SET allow_experimental_object_type = 1;
   CREATE TABLE t (data Object('json')) ENGINE = MergeTree() ORDER BY tuple();
   

5. Check your ClickHouse version:

   SELECT version();
   

   Compare against the documentation to see when the type you need was introduced.

Best Practices

• Refer to the official ClickHouse documentation for the exact type names and their required syntax.
• When migrating schemas from other databases, create a type mapping table and convert systematically.
• Use SELECT toTypeName(expression) to discover how ClickHouse names types internally.
• Keep ClickHouse updated if you need newer types like Bool, JSON, or Dynamic.

Frequently Asked Questions

Q: Does ClickHouse support BOOLEAN?
A: Recent ClickHouse versions support Bool as an alias for UInt8. Some versions also accept BOOLEAN as a compatibility alias. If yours doesn't, use UInt8 directly.

Q: Why is the type name case-sensitive?
A: ClickHouse type names follow a specific convention (e.g., UInt32, not uint32). This is by design for consistency with the codebase. Always match the documented capitalization.

Q: I need JSON storage. What type should I use?
A: In ClickHouse 23.1+, you can use the JSON type (previously Object('json')). In older versions, store JSON as String and use JSON extraction functions at query time. Check your version's documentation for the current recommended approach.

Q: Can I create custom types in ClickHouse?
A: ClickHouse does not support user-defined types. However, you can use Tuple with named fields to create structured column types, and Enum for fixed value sets.