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

user_9632703 0
Viewed 1318

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.

clickhouse
2 Answers

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.

user_177852020

Clickhouse behaviour with Not Null constraints is not compatible with other databases.

You can overcome it using check constraints https://clickhouse.com/docs/en/sql-reference/statements/create/table/#constraints

CREATE TABLE test (
    name String NOT NULL,
    isodate DateTime('Europe/Berlin') NOT NULL,
    CONSTRAINT isodate_not_null CHECK isodate <> toDateTime(0, 'Europe/Berlin')
) ENGINE = MergeTree()
ORDER BY (isodate)


insert into test(name) values ('x');
DB::Exception: Constraint `isodate_not_null` for table default.test (f589312a-1592-426a-b589-312a1592b26a) is violated at row 1. Expression: (isodate != toDateTime(0)). Column values: isodate = 0. (VIOLATED_CONSTRAINT)


insert into test values ('x', now());
OK.

The reason is performance, in OLAP databases need to ingest data as fast as possible.

user_116443080
Related