Skip to main content
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:

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:
Possible values:
  • 0 — Disabled.
  • Any number from 1 to shards_num of corresponding Distributed table.
Example Query:
Result:

insert_squashing_max_memory_ratio

Caps min_insert_block_size_bytes by this fraction of the server’s memory limit (max_server_memory_usage) in the squashing stages of an INSERT: the squashing for the target table, the squashing of the results of dependent materialized views, and the block size of the source of a trivial INSERT ... SELECT. A squashing stage does not hold one block of min_insert_block_size_bytes but several: it accumulates the next block while the previous one is concatenated, the concatenated block is alive in the sink, and blocks are measured by their logical size while the arrays behind them round their capacity up. On a server with little memory that can make an INSERT fail with MEMORY_LIMIT_EXCEEDED. With the default min_insert_block_size_bytes the default value binds only below about 2.2 GiB of memory limit; a lower value such as 0.0625 helps an INSERT into a wide table fit into the memory of a small server. The cap depends only on the memory limit and this setting, not on the number of materialized views or insert streams. Note that the memory limit can change at runtime (see memory_worker_dynamic_hard_limit), and when the cap binds, a retried INSERT may be split into blocks differently than the first attempt. Possible values: from 0 to 1. Set to 0 to disable the cap.