How can I decline an INSERT when column is set to NOT NULL

From the documentation, you have to put a NOT NULL modifier in the column definition to mark it as such, just like for other SQL databases.

Consider this table:

CREATE TABLE test (
    name String NOT NULL,
    isodate DateTime('Europe/Berlin') NOT NULL
) ENGINE = MergeTree()
ORDER BY (isodate)

If I would try to insert NULL for both columns (or at least one), the expected behaviour is that Clickhouse declines insertion since the columns are marked as NOT NULL. Instead, Clickhouse creates a new row, where isodate is 1970-01-01 01:00:00 and name is an empty string, which are the default values for those data types apparently.

What do I have to do so that Clickhouse declines such inserts?

My Clickhouse server version is 21.12.3.

Solution

In ClickHouse, NULL and NOT NULL do change the behavior of the data type, but not in the way other relational databases - it is syntactically compatible with other relational database but not semantically (a Int32 NULL is the same as a Nullable(Int32), as a Int32 NOT NULL is the same as a Int32). A column defined as NOT NULL does not mean it will refuse to insert fields whose values are NULL in the insert statement - it means ClickHouse will use the default expression for the column type (or if it is not specified in the column definition, the default value for the data type). This behavior is expected in ClickHouse when input_format_null_as_default is enabled (the default for Clickhouse 21.12.3).

To throw exceptions for such invalid values you need to change the system setting input_format_null_as_default to 0. If you use clickhouse-client, you can disable it while connecting to clickhouse:

clickhouse-client -h ... --input_format_null_as_default 0

or after:

clickhouse> SET input_format_null_as_default=0

This way, a statement like insert into test (name, isodate) values (NULL, NULL); will behave more likely most relational databases.