v.23.2Improvement

Insert Null as Default Behavior and Fix for LowCardinality Columns

Official CH changelog text
When insert_null_as_default is enabled and column doesn't have defined default value, the default of column type will be used. Also this PR fixes using default values on nulls in case of LowCardinality columns. #46171 (Kruglov Pavel).
insert_null_as_default Setting Enhances Null Insert Behavior with Default Values

When enabled, this option replaces inserted NULL values with the column's default value if the column does not have a user-defined default, using the type's default instead. It also fixes handling default values for NULL in LowCardinality columns.

Why it matters

This feature solves the problem where inserting NULL into columns without explicitly defined default values could lead to errors or inconsistent behavior. It ensures that such NULL values are automatically replaced with appropriate default values, improving data consistency and simplifying data insertion workflows, especially for LowCardinality columns.

How to use it

Enable the feature by setting the insert_null_as_default parameter to true during insertion. For example:

INSERT INTO table_name (column1, column2) VALUES (value1, NULL) SETTINGS insert_null_as_default = 1;

This will insert the default value for the column where NULL is specified if there is no user-defined default value.

Related resources

PreviousNext