ClickHouse DB::Exception: Cannot insert NULL in ordinary column

The "DB::Exception: Cannot insert NULL in ordinary column" error is one of the most frequently encountered ClickHouse errors. It occurs when an INSERT attempts to write a NULL value into a column that is not declared as Nullable. By default, ClickHouse columns do not accept NULL — you must explicitly opt in by declaring the column as Nullable(T).

Impact

The INSERT statement fails, which can block data ingestion pipelines, application writes, and materialized view population. This is particularly common when importing data from systems like PostgreSQL or MySQL where columns are nullable by default, or when consuming JSON data where fields may be absent.

Common Causes

1. Source data contains NULLs but the target column is not Nullable — the most common scenario, especially with data migrations.
2. JSON input with missing fields — absent JSON keys are interpreted as NULL.
3. LEFT JOIN producing NULLs — an INSERT ... SELECT with a LEFT JOIN where unmatched rows generate NULLs for right-side columns.
4. CSV/TSV with empty fields — depending on format settings, empty fields may be parsed as NULL.
5. Application code sending NULL — ORMs or drivers that send NULL for unset fields.
6. Materialized view SELECT that produces NULLs — e.g., using JSONExtract that returns NULL on missing keys.

Troubleshooting and Resolution Steps

1. Identify the column causing the error. The error message includes the column name.

2. Make the column Nullable if NULL values are legitimate:

   ALTER TABLE your_table MODIFY COLUMN col_name Nullable(String);
   

3. Provide default values instead of NULL. Use ifNull() or coalesce() in your INSERT ... SELECT:

   INSERT INTO your_table
   SELECT
       ifNull(nullable_col, 'default_value') AS col_name
   FROM source_table;
   

4. Set input_format_null_as_default for file-based imports. This replaces NULLs with the column's default value:

   SET input_format_null_as_default = 1;
   INSERT INTO your_table FORMAT CSV ...;
   

5. Handle NULLs from JOINs explicitly:

   INSERT INTO your_table
   SELECT
       a.id,
       ifNull(b.name, '') AS name
   FROM table_a AS a
   LEFT JOIN table_b AS b ON a.id = b.id;
   

6. Add a DEFAULT expression to the column so ClickHouse uses it when no value is provided:

   ALTER TABLE your_table MODIFY COLUMN col_name String DEFAULT 'unknown';
   

7. For JSON imports, use appropriate extraction with defaults:

   SELECT
       JSONExtractString(json, 'name', 'default_name') AS name
   FROM input_table;
   

Best Practices

• Decide upfront which columns should allow NULLs and declare them as Nullable(T). Be intentional — Nullable adds a bitmask overhead per column.
• Use input_format_null_as_default = 1 as a global setting for data import pipelines to handle NULLs gracefully.
• When migrating from other databases, audit the source schema for nullable columns and mirror that in ClickHouse where needed.
• Prefer DEFAULT expressions over Nullable when a sensible default value exists — this avoids the overhead of Nullable and simplifies query logic.
• In application code, convert NULLs to default values before sending data to ClickHouse.

Frequently Asked Questions

Q: Should I make all columns Nullable to avoid this error?
A: No. Nullable columns have storage and performance overhead (an additional bitmask per column) and complicate query logic. Only make columns Nullable when NULL is a meaningful state in your data model. Use DEFAULT values where appropriate instead.

Q: Does input_format_null_as_default work for all formats?
A: It works for most input formats including CSV, TSV, JSON, and Parquet. When enabled, NULL values in the input are replaced with the column's DEFAULT value (or the type's zero value if no DEFAULT is defined).

Q: What is the default value for a non-Nullable column with no DEFAULT clause?
A: ClickHouse uses the type's "zero value": 0 for numbers, empty string for String, 1970-01-01 for Date, etc. These are used when input_format_null_as_default replaces NULLs.

Q: Can I change a Nullable column back to non-Nullable?
A: Yes, with ALTER TABLE ... MODIFY COLUMN col_name Type (without Nullable). Existing NULL values will be replaced with the type's zero value. Be aware this rewrites all parts containing the column.