ClickHouse DB::Exception: Partition already exists (Code: 256)

The "DB::Exception: PARTITION_ALREADY_EXISTS" error in ClickHouse occurs when an ATTACH PARTITION operation attempts to add a partition that already exists in the target table. This error corresponds to ClickHouse error code PARTITION_ALREADY_EXISTS and prevents accidental data duplication from attaching overlapping partitions.

Impact

The ATTACH PARTITION operation fails, but the existing data in the table remains unaffected. The partition data in the detached directory is not modified and can be retried or handled differently.

Common Causes

1. Partition already present in the table — the target table already contains data parts belonging to the same partition you are trying to attach.
2. Repeated ATTACH operation — running the same ATTACH PARTITION statement more than once after the first one succeeded.
3. Partition moved from another table — moving a partition from one table to another where the same partition already exists.
4. Backup restoration overlap — restoring a partition from backup when the table already contains current data for that partition.
5. Data migration scripts — automated scripts that attach partitions without checking if they already exist.

Troubleshooting and Resolution Steps

1. Check existing partitions in the target table:

   SELECT partition, name, rows, bytes_on_disk
   FROM system.parts
   WHERE database = 'your_database' AND table = 'your_table' AND active
   ORDER BY partition;
   

2. If you want to replace the existing partition, drop it first:

   ALTER TABLE your_database.your_table DROP PARTITION 'partition_id';
   ALTER TABLE your_database.your_table ATTACH PARTITION 'partition_id';
   

3. Use REPLACE PARTITION to atomically swap the partition:

   ALTER TABLE your_database.target_table
       REPLACE PARTITION 'partition_id'
       FROM your_database.source_table;
   

   This replaces the existing partition data with the source partition in one atomic operation.

4. Check the detached directory for the partition to be attached:

   ls /var/lib/clickhouse/data/your_database/your_table/detached/
   

5. If you need to merge the data rather than replace, insert the data from a temporary table:

   -- Create a temporary table, attach the part there, then INSERT SELECT
   CREATE TABLE your_database.temp_table AS your_database.your_table;
   ALTER TABLE your_database.temp_table ATTACH PARTITION 'partition_id';
   INSERT INTO your_database.your_table SELECT * FROM your_database.temp_table;
   DROP TABLE your_database.temp_table;
   

6. Guard against duplicates in scripts:

   -- Check before attaching
   SELECT count() FROM system.parts
   WHERE database = 'your_database'
       AND table = 'your_table'
       AND partition = 'partition_id'
       AND active;
   

Best Practices

• Use REPLACE PARTITION when you want to update partition data atomically without worrying about existing data.
• Add existence checks to data migration and backup restoration scripts before attaching partitions.
• Document your partitioning scheme and data lifecycle to avoid confusion about which partitions should exist.
• When moving data between tables, prefer ALTER TABLE ... MOVE PARTITION ... TO TABLE for atomic operations.
• Test partition management operations in a staging environment before running them in production.

Frequently Asked Questions

Q: What is the difference between ATTACH PARTITION and REPLACE PARTITION?
A: ATTACH PARTITION adds a partition from the detached directory and fails if the partition already exists. REPLACE PARTITION atomically swaps the partition data from a source table into the target table, replacing any existing data for that partition.

Q: Can I attach individual parts instead of entire partitions?
A: Yes. You can use ALTER TABLE ... ATTACH PART 'part_name' to attach individual parts, which gives more granular control and may avoid conflicts with existing partitions.

Q: Does dropping a partition before attaching risk data loss?
A: Yes. If the DROP PARTITION succeeds but the ATTACH fails for any reason, you would lose the original partition data. Using REPLACE PARTITION from a source table is safer because it performs the operation atomically.

Q: Can this error occur with ReplicatedMergeTree tables?
A: Yes. The same partition existence check applies to replicated tables. The ATTACH or REPLACE operation will be replicated to all replicas via ZooKeeper.