> ## Documentation Index
> Fetch the complete documentation index at: https://clickhouse.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# insert_* session settings

> ClickHouse session settings in the insert_* generated group.

export const SettingsInfoBlock = ({type, default_value, changeable_without_restart}) => {
  return <div className="not-prose" style={{
    display: "flex",
    flexWrap: "wrap",
    alignItems: "baseline",
    columnGap: "0.5rem",
    rowGap: "0.125rem",
    margin: "0.375rem 0",
    fontSize: "0.8125rem",
    lineHeight: "1.125rem"
  }}>
      <div style={{
    fontWeight: 600,
    opacity: 0.72
  }}>Type</div>
      <div style={{
    overflowWrap: "anywhere"
  }}>{type}</div>
      <div style={{
    fontWeight: 600,
    opacity: 0.72,
    marginInlineStart: "0.5rem"
  }}>Default</div>
      <div style={{
    overflowWrap: "anywhere"
  }}>{default_value}</div>
      {changeable_without_restart && <div style={{
    fontWeight: 600,
    opacity: 0.72,
    marginInlineStart: "0.5rem"
  }}>
          Changeable without restart
        </div>}
      {changeable_without_restart && <div style={{
    overflowWrap: "anywhere"
  }}>
          {changeable_without_restart}
        </div>}
    </div>;
};

These settings are available in [system.settings](/docs/reference/system-tables/settings) and are autogenerated from [source](https://github.com/ClickHouse/ClickHouse/blob/master/src/Core/Settings.cpp).

<h2 id="insert_allow_materialized_columns">
  insert\_allow\_materialized\_columns
</h2>

<SettingsInfoBlock type="Bool" default_value="0" />

If setting is enabled, Allow materialized columns in INSERT.

<h2 id="insert_deduplicate">
  insert\_deduplicate
</h2>

<SettingsInfoBlock type="Bool" default_value="1" />

Enables or disables block deduplication of `INSERT` (for Replicated\* tables).

Possible values:

* 0 — Disabled.
* 1 — Enabled.

By default, blocks inserted into replicated tables by the `INSERT` statement are deduplicated (see [Data Replication](/docs/reference/engines/table-engines/mergetree-family/replication)).
For the replicated tables by default the only 100 of the most recent blocks for each partition are deduplicated (see [replicated\_deduplication\_window](/docs/reference/settings/merge-tree-settings#replicated_deduplication_window), [replicated\_deduplication\_window\_seconds](/docs/reference/settings/merge-tree-settings#replicated_deduplication_window_seconds)).
For not replicated tables see [non\_replicated\_deduplication\_window](/docs/reference/settings/merge-tree-settings#non_replicated_deduplication_window).

<h2 id="insert_deduplication_token">
  insert\_deduplication\_token
</h2>

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](/docs/reference/settings/merge-tree-settings#replicated_deduplication_window), [replicated\_deduplication\_window\_seconds](/docs/reference/settings/merge-tree-settings#replicated_deduplication_window_seconds)).
For not replicated tables see [non\_replicated\_deduplication\_window](/docs/reference/settings/merge-tree-settings#non_replicated_deduplication_window).

<Note>
  `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.
</Note>

Example:

```sql theme={null}
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 │
└───┘
```

<h2 id="insert_null_as_default">
  insert\_null\_as\_default
</h2>

<SettingsInfoBlock type="Bool" default_value="1" />

Enables or disables the insertion of [default values](/docs/reference/statements/create/table#default_values) instead of [NULL](/docs/reference/syntax#null) into columns with not [nullable](/docs/reference/data-types/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](/docs/reference/statements/insert-into#inserting-the-results-of-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`.

<h2 id="insert_shard_id">
  insert\_shard\_id
</h2>

<SettingsInfoBlock type="UInt64" default_value="0" />

If not `0`, specifies the shard of [Distributed](/docs/reference/engines/table-engines/special/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:

```sql theme={null}
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](/docs/reference/engines/table-engines/special/distributed) table.

**Example**

Query:

```sql theme={null}
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:

```text theme={null}
┌─number─┐
│      0 │
│      0 │
│      1 │
│      1 │
│      2 │
│      2 │
│      3 │
│      3 │
│      4 │
│      4 │
└────────┘
```