title_wip

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/statements/alter/partition.md)

The following operations with [partitions](/docs/engines/table-engines/mergetree-family/custom-partitioning-key) are available:

- [DETACH PARTITION|PART](#detach-partitionpart) — Moves a partition or part to the `detached` directory and forget it.
- [DROP PARTITION|PART](#drop-partitionpart) — Deletes a partition or part.
- [DROP DETACHED PARTITION|PART](#drop-detached-partitionpart) - Delete a part or all parts of a partition from `detached`.
- [FORGET PARTITION](#forget-partition) — Deletes a partition metadata from zookeeper if it's empty.
- [ATTACH PARTITION|PART](#attach-partitionpart) — Adds a partition or part from the `detached` directory to the table.
- [ATTACH PARTITION FROM](#attach-partition-from) — Copies the data partition from one table to another and adds.
- [REPLACE PARTITION](#replace-partition) — Copies the data partition from one table to another and replaces.
- [MOVE PARTITION TO TABLE](#move-partition-to-table) — Moves the data partition from one table to another.
- [CLEAR COLUMN IN PARTITION](#clear-column-in-partition) — Resets the value of a specified column in a partition.
- [CLEAR INDEX IN PARTITION](#clear-index-in-partition) — Resets the specified secondary index in a partition.
- [FREEZE PARTITION](#freeze-partition) — Creates a backup of a partition.
- [UNFREEZE PARTITION](#unfreeze-partition) — Removes a backup of a partition.
- [FETCH PARTITION|PART](#fetch-partitionpart) — Downloads a part or partition from another server.
- [MOVE PARTITION|PART](#move-partitionpart) — Move partition/data part to another disk or volume.
- [UPDATE IN PARTITION](#update-in-partition) — Update data inside the partition by condition.
- [DELETE IN PARTITION](#delete-in-partition) — Delete data inside the partition by condition.
- [REWRITE PARTS](#rewrite-parts) — Rewrite parts in the table (or specific partition) completely.

## DETACH PARTITION|PART[​](#detach-partitionpart "Direct link to DETACH PARTITION|PART")

```
ALTER TABLE table_name [ON CLUSTER cluster] DETACH PARTITION|PART partition_expr
```

Moves all data for the specified partition to the `detached` directory. The server forgets about the detached data partition as if it does not exist. The server will not know about this data until you make the [ATTACH](#attach-partitionpart) query.

Example:

```
ALTER TABLE mt DETACH PARTITION '2020-11-21';
ALTER TABLE mt DETACH PART 'all_2_2_0';
```

Read about setting the partition expression in a section [How to set the partition expression](#how-to-set-partition-expression).

After the query is executed, you can do whatever you want with the data in the `detached` directory — delete it from the file system, or just leave it.

This query is replicated – it moves the data to the `detached` directory on all replicas. Note that you can execute this query only on a leader replica. To find out if a replica is a leader, perform the `SELECT` query to the [system.replicas](/docs/operations/system-tables/replicas) table. Alternatively, it is easier to make a `DETACH` query on all replicas - all the replicas throw an exception, except the leader replicas (as multiple leaders are allowed).

## DROP PARTITION|PART[​](#drop-partitionpart "Direct link to DROP PARTITION|PART")

```
ALTER TABLE table_name [ON CLUSTER cluster] DROP PARTITION|PART partition_expr
```

Deletes the specified partition from the table. This query tags the partition as inactive and deletes data completely, approximately in 10 minutes.

Read about setting the partition expression in a section [How to set the partition expression](#how-to-set-partition-expression).

The query is replicated – it deletes data on all replicas.

Example:

```
ALTER TABLE mt DROP PARTITION '2020-11-21';
ALTER TABLE mt DROP PART 'all_4_4_0';
```

## DROP DETACHED PARTITION|PART[​](#drop-detached-partitionpart "Direct link to DROP DETACHED PARTITION|PART")

```
ALTER TABLE table_name [ON CLUSTER cluster] DROP DETACHED PARTITION|PART ALL|partition_expr
```

Removes the specified part or all parts of the specified partition from `detached`.
Read more about setting the partition expression in a section [How to set the partition expression](#how-to-set-partition-expression).

## FORGET PARTITION[​](#forget-partition "Direct link to FORGET PARTITION")

```
ALTER TABLE table_name FORGET PARTITION partition_expr
```

Removes all metadata about an empty partition from ZooKeeper. Query fails if partition is not empty or unknown. Make sure to execute only for partitions that will never be used again.

Read about setting the partition expression in a section [How to set the partition expression](#how-to-set-partition-expression).

Example:

```
ALTER TABLE mt FORGET PARTITION '20201121';
```

## ATTACH PARTITION|PART[​](#attach-partitionpart "Direct link to ATTACH PARTITION|PART")

```
ALTER TABLE table_name ATTACH PARTITION|PART partition_expr
```

Adds data to the table from the `detached` directory. It is possible to add data for an entire partition or for a separate part. Examples:

```
ALTER TABLE visits ATTACH PARTITION 201901;
ALTER TABLE visits ATTACH PART 201901_2_2_0;
```

Read more about setting the partition expression in a section [How to set the partition expression](#how-to-set-partition-expression).

This query is replicated. The replica-initiator checks whether there is data in the `detached` directory.
If data exists, the query checks its integrity. If everything is correct, the query adds the data to the table.

If the non-initiator replica, receiving the attach command, finds the part with the correct checksums in its own `detached` folder, it attaches the data without fetching it from other replicas.
If there is no part with the correct checksums, the data is downloaded from any replica having the part.

You can put data to the `detached` directory on one replica and use the `ALTER ... ATTACH` query to add it to the table on all replicas.

## ATTACH PARTITION FROM[​](#attach-partition-from "Direct link to ATTACH PARTITION FROM")

```
ALTER TABLE table2 [ON CLUSTER cluster] ATTACH PARTITION partition_expr FROM table1
```

This query copies the data partition from `table1` to `table2`.

Note that:

- Data will be deleted neither from `table1` nor from `table2`.
- `table1` may be a temporary table.

For the query to run successfully, the following conditions must be met:

- Both tables must have the same structure.
- Both tables must have the same partition key, the same order by key and the same primary key.
- Both tables must have the same storage policy.
- The destination table must include all indices and projections from the source table. If the `enforce_index_structure_match_on_partition_manipulation` setting is enabled in destination table, the indices and projections must be identical. Otherwise, the destination table can have a superset of the source table's indices and projections.

## REPLACE PARTITION[​](#replace-partition "Direct link to REPLACE PARTITION")

```
ALTER TABLE table2 [ON CLUSTER cluster] REPLACE PARTITION partition_expr FROM table1
```

This query copies the data partition from `table1` to `table2` and replaces the existing partition in `table2`. The operation is atomic.

Note that:

- Data won't be deleted from `table1`.
- `table1` may be a temporary table.

For the query to run successfully, the following conditions must be met:

- Both tables must have the same structure.
- Both tables must have the same partition key, the same order by key and the same primary key.
- Both tables must have the same storage policy.
- The destination table must include all indices and projections from the source table. If the `enforce_index_structure_match_on_partition_manipulation` setting is enabled in destination table, the indices and projections must be identical. Otherwise, the destination table can have a superset of the source table's indices and projections.

## MOVE PARTITION TO TABLE[​](#move-partition-to-table "Direct link to MOVE PARTITION TO TABLE")