ClickHouse DB::Exception: There is no default value for column

The THERE_IS_NO_DEFAULT_VALUE error occurs when an INSERT statement omits a column that has no DEFAULT, MATERIALIZED, or ALIAS expression defined. ClickHouse raises something like DB::Exception: There is no default value for column 'user_id'. Unlike some databases that implicitly insert NULL or a zero value for missing columns, ClickHouse requires that every non-defaulted column be explicitly included in the INSERT unless a default expression exists in the table schema.

Impact

The INSERT statement fails entirely -- no rows are written. This can disrupt data pipelines, batch loading jobs, and application writes. If the error occurs in a streaming ingestion pipeline (e.g., Kafka engine or a programmatic INSERT loop), it may cause message backlog or data loss depending on how the pipeline handles failures.

Common Causes

INSERT column list does not include all required columns -- The INSERT INTO table (col1, col2) syntax was used, but col3 has no default and was omitted.
Schema evolution mismatch -- A new column was added to the table without a DEFAULT expression, and existing INSERT statements were not updated to include it.
Application code out of sync with schema -- The application generates INSERT statements based on an outdated schema definition.
Using INSERT INTO table VALUES with too few values -- The positional value list does not match the number of columns.
Materialized view target table -- A materialized view inserts into a target table, but the SELECT does not produce all required columns.

Troubleshooting and Resolution Steps

Identify the missing column: The error message specifies which column lacks a default value. Check the table schema:
```
SHOW CREATE TABLE your_table;
```
Or:
```
DESCRIBE TABLE your_table;
```
Look for columns without DEFAULT, MATERIALIZED, or ALIAS expressions.

Add the missing column to your INSERT:

-- Before (missing user_id)
INSERT INTO events (event_type, timestamp) VALUES ('click', now());

-- After (including user_id)
INSERT INTO events (event_type, timestamp, user_id) VALUES ('click', now(), 42);

Add a DEFAULT expression to the column: If the column can have a sensible default, alter the table:

ALTER TABLE events MODIFY COLUMN user_id UInt64 DEFAULT 0;

Or for nullable columns:

ALTER TABLE events MODIFY COLUMN user_id Nullable(UInt64) DEFAULT NULL;

For schema evolution, add new columns with defaults: When adding columns to a table that has active writers, always specify a DEFAULT:
```
ALTER TABLE events ADD COLUMN region String DEFAULT 'unknown';
```
This way, existing INSERT statements continue to work without modification.
Fix materialized view definitions: If the error comes from a materialized view, ensure the view's SELECT produces all columns the target table requires:
```
-- Check the MV definition
SHOW CREATE TABLE your_materialized_view;
```
Add the missing column to the SELECT or add a DEFAULT to the target table.
Use input_format_defaults_for_omitted_fields: For certain input formats (CSV, JSONEachRow, etc.), this setting controls whether ClickHouse applies column defaults for omitted fields:
```
SET input_format_defaults_for_omitted_fields = 1;
```
Note that this only works when a DEFAULT expression is defined -- it does not help if the column truly has no default.

Best Practices

Always define DEFAULT expressions for columns when creating tables, especially for columns that might not be present in every INSERT.
When evolving a schema, add new columns with a DEFAULT value to maintain backward compatibility with existing writers.
Keep application INSERT schemas in sync with the database schema; use schema migration tools.
For tables receiving data from multiple sources, use Nullable types or explicit defaults as a safety net.
Test INSERT statements against the actual schema in a staging environment before deploying changes.

Frequently Asked Questions

Q: Can I make ClickHouse insert a zero or empty value for missing columns automatically?
A: Only if the column has a DEFAULT expression. You can set ALTER TABLE ... MODIFY COLUMN col_name Type DEFAULT value to add a default. Without a DEFAULT, ClickHouse will not guess what value to use.

Q: Does this error apply to INSERT SELECT as well?
A: Yes. If your INSERT INTO target SELECT ... FROM source does not produce values for all non-default columns in the target table, you will get this error. Make sure the SELECT includes or aliases all required columns.

Q: What is the difference between DEFAULT, MATERIALIZED, and ALIAS?
A: DEFAULT provides a value when the column is omitted in an INSERT and is stored on disk. MATERIALIZED is computed at INSERT time and stored, but cannot be directly specified in an INSERT. ALIAS is computed at query time and is not stored. All three prevent the THERE_IS_NO_DEFAULT_VALUE error.

Q: I am using Kafka engine and getting this error. How do I fix it?
A: The Kafka engine reads from a topic and the materialized view inserts into a target table. Ensure the materialized view's SELECT maps all required columns of the target table. Add DEFAULT expressions to any target table columns that the Kafka message does not provide.