insert_* session settings

These settings are available in system.settings and are autogenerated from source.

insert_allow_materialized_columns

If setting is enabled, Allow materialized columns in INSERT.

insert_deduplicate

Enables or disables block deduplication of INSERT (for Replicated* tables). This setting only takes effect when deduplicate_insert is set to backward_compatible_choice. Possible values:

0 — Disabled.
1 — Enabled.

By default, blocks inserted into replicated tables by the INSERT statement are deduplicated (see Data Replication). For the replicated tables by default the only 100 of the most recent blocks for each partition are deduplicated (see replicated_deduplication_window, replicated_deduplication_window_seconds). For not replicated tables see non_replicated_deduplication_window.

insert_deduplication_token

The setting allows a user to provide own deduplication semantic in MergeTree/ReplicatedMergeTree For example, by providing a unique value for the setting in each INSERT statement, user can avoid the same inserted data being deduplicated. Possible values:

Any string

insert_deduplication_token is used for deduplication only when not empty. For the replicated tables by default the only 100 of the most recent inserts for each partition are deduplicated (see replicated_deduplication_window, replicated_deduplication_window_seconds). For not replicated tables see non_replicated_deduplication_window.

insert_deduplication_token is tracked per partition, so multiple partitions written by one insert can carry the same token. Without a token, the default content checksum is computed over the whole inserted block, so an insert is deduplicated only when its entire data matches a previous insert (a retry), not when a single partition’s rows happen to coincide with a different insert.

Example:

CREATE TABLE test_table
( A Int64 )
ENGINE = MergeTree
ORDER BY A
SETTINGS non_replicated_deduplication_window = 100;

INSERT INTO test_table SETTINGS insert_deduplication_token = 'test' VALUES (1);

-- the next insert won't be deduplicated because insert_deduplication_token is different
INSERT INTO test_table SETTINGS insert_deduplication_token = 'test1' VALUES (1);

-- the next insert will be deduplicated because insert_deduplication_token
-- is the same as one of the previous
INSERT INTO test_table SETTINGS insert_deduplication_token = 'test' VALUES (2);

SELECT * FROM test_table

┌─A─┐
│ 1 │
└───┘
┌─A─┐
│ 1 │
└───┘

insert_null_as_default

Enables or disables the insertion of default values instead of NULL into columns with not nullable data type. If column type is not nullable and this setting is disabled, then inserting NULL causes an exception. If column type is nullable, then NULL values are inserted as is, regardless of this setting. This setting is applicable to INSERT … SELECT queries. Note that SELECT subqueries may be concatenated with UNION ALL clause. Possible values:

0 — Inserting NULL into a not nullable column causes an exception.
1 — Default column value is inserted instead of NULL.

insert_shard_id

If not 0, specifies the shard of Distributed table into which the data will be inserted synchronously. If insert_shard_id value is incorrect, the server will throw an exception. To get the number of shards on requested_cluster, you can check server config or use this query:

SELECT uniq(shard_num) FROM system.clusters WHERE cluster = 'requested_cluster';

Possible values:

0 — Disabled.
Any number from 1 to shards_num of corresponding Distributed table.

Example Query:

CREATE TABLE x AS system.numbers ENGINE = MergeTree ORDER BY number;
CREATE TABLE x_dist AS x ENGINE = Distributed('test_cluster_two_shards_localhost', currentDatabase(), x);
INSERT INTO x_dist SELECT * FROM numbers(5) SETTINGS insert_shard_id = 1;
SELECT * FROM x_dist ORDER BY number ASC;

Result:

┌─number─┐
│      0 │
│      0 │
│      1 │
│      1 │
│      2 │
│      2 │
│      3 │
│      3 │
│      4 │
│      4 │
└────────┘

SQL Reference

Data Types

Engines

Functions

Formats

Settings

System Tables

Data Lakes

insert_allow_materialized_columns

insert_deduplicate

insert_deduplication_token

insert_null_as_default

insert_shard_id

​insert_allow_materialized_columns

​insert_deduplicate

​insert_deduplication_token

​insert_null_as_default

​insert_shard_id

insert_allow_materialized_columns

insert_deduplicate

insert_deduplication_token

insert_null_as_default

insert_shard_id