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.

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

┌

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 │
└────────┘