ClickHouse DB::Exception: Data type cannot be used in tables

The "DB::Exception: Data type cannot be used in tables" error occurs when you try to create or alter a table with a column type that ClickHouse cannot persist to disk. Certain types exist only for intermediate query processing and have no serialization format for storage. The most common offender is Nothing, but other internal types can also trigger this error.

Impact

The CREATE TABLE or ALTER TABLE statement fails. You cannot store data of this type on disk. The fix is to choose a concrete, persistable type instead.

Common Causes

1. Using the Nothing type in a column definition — Nothing represents an empty or undefined type and cannot be stored.
2. Column type inferred as Nothing — a CREATE TABLE ... AS SELECT where the SELECT produces a Nothing-typed column (e.g., from SELECT NULL without a cast).
3. Set type in a column definition — the Set type is used internally for subquery evaluation and is not persistable.
4. Experimental or internal types — some types exist only for ClickHouse's internal processing pipeline.
5. CREATE TABLE AS SELECT with empty results — when type inference produces an internal type because no concrete data was available.

Troubleshooting and Resolution Steps

1. Check which column has the non-persistable type. The error message names the column and type.

2. Replace Nothing with a concrete Nullable type:

   -- Wrong: produces Nothing type
   CREATE TABLE t ENGINE = MergeTree() ORDER BY tuple() AS
   SELECT NULL AS col;
   
   -- Correct: cast to a concrete Nullable type
   CREATE TABLE t ENGINE = MergeTree() ORDER BY tuple() AS
   SELECT CAST(NULL AS Nullable(String)) AS col;
   

3. Explicitly define column types in CREATE TABLE rather than relying on type inference:

   CREATE TABLE t (
       id UInt64,
       value Nullable(String)
   ) ENGINE = MergeTree() ORDER BY id;
   

4. Check the types produced by your SELECT before using it in CREATE TABLE AS:

   SELECT toTypeName(col1), toTypeName(col2) FROM (your_select) LIMIT 1;
   

5. If you need a placeholder column, use Nullable(String) or UInt8 as a concrete stand-in rather than allowing type inference to produce Nothing.

Best Practices

• Always specify explicit column types in table definitions rather than relying on type inference from SELECT statements.
• When using CREATE TABLE ... AS SELECT, verify the inferred types with toTypeName() first.
• Avoid SELECT NULL without an explicit CAST in table creation contexts.
• If your query logic can produce a Nothing type (e.g., conditional branches that all return NULL), wrap the result in a CAST to a concrete type.

Frequently Asked Questions

Q: What exactly is the Nothing type?
A: Nothing is ClickHouse's bottom type — it represents a value that has no concrete type. It appears when ClickHouse cannot determine a type, such as when the only value is an untyped NULL. It's valid in query expressions but cannot be stored in a table.

Q: Can I use Nullable(Nothing)?
A: Nullable(Nothing) can appear in query results but cannot be persisted in a table either. You need a concrete base type like Nullable(String) or Nullable(Int32).

Q: Why does CREATE TABLE AS SELECT sometimes produce Nothing columns?
A: If a column in the SELECT evaluates to only NULL values without any type context, ClickHouse infers the type as Nullable(Nothing). Adding an explicit CAST gives ClickHouse the concrete type it needs.

Q: Are there other types besides Nothing that can't be used in tables?
A: Yes, some internal types like Set (used for IN subqueries) and certain intermediate aggregate function states cannot be stored directly. In practice, Nothing is by far the most commonly encountered case.