ClickHouse DB::Exception: Invalid partition value

The "DB::Exception: Invalid partition value" error in ClickHouse occurs when a value specified in a partition-related operation does not match the expected type or format defined by the table's partition key expression. The error code is INVALID_PARTITION_VALUE. This typically happens in ALTER TABLE ... DROP PARTITION, DETACH PARTITION, or similar DDL commands when the provided partition identifier is incompatible with the table's partition key.

Impact

The DDL operation targeting the partition fails. No data is dropped, detached, or moved. The table itself remains unaffected. You need to provide the partition value in the correct format before the operation can proceed.

Common Causes

1. Wrong type for the partition value -- Providing a string when the partition key expects an integer, or vice versa.
2. Incorrect date format -- The partition key is based on a Date or DateTime column, but the partition value is specified in the wrong format.
3. Tuple vs scalar mismatch -- The partition key is a composite expression (tuple) but only a single scalar value was provided.
4. Using the partition name instead of the partition expression -- Confusing the partition ID (like 202301) with the partition expression value.
5. Missing toYYYYMM or similar transformation -- The partition key uses toYYYYMM(date_column) but the ALTER statement passes a raw date.

Troubleshooting and Resolution Steps

1. Check the table's partition key expression:

   SELECT partition_key FROM system.tables
   WHERE database = 'your_db' AND name = 'your_table';
   

2. List existing partitions to see the correct format:

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

3. Use the correct partition value format. Match the type of the partition key expression:

   -- If partition key is toYYYYMM(date_col), use a number:
   ALTER TABLE your_table DROP PARTITION 202301;
   
   -- If partition key is a Date column, use a date string:
   ALTER TABLE your_table DROP PARTITION '2023-01-15';
   
   -- If partition key is a tuple:
   ALTER TABLE your_table DROP PARTITION ('US', 202301);
   

4. Use partition ID instead of partition expression if you are unsure about the format:

   -- Get the partition ID from system.parts
   SELECT DISTINCT partition_id FROM system.parts
   WHERE database = 'your_db' AND table = 'your_table';
   
   -- Use it directly
   ALTER TABLE your_table DROP PARTITION ID '202301';
   

5. Verify the partition expression result type:

   -- See what type the partition expression produces
   SELECT toYYYYMM(today()) AS partition_val, toTypeName(partition_val);
   

Best Practices

• Always check system.parts for the actual partition values before running partition-level DDL operations.
• Use DROP PARTITION ID with the partition ID string when you want to avoid type-matching issues.
• Document your table's partition key expression alongside the table schema so operators know the correct format.
• Test partition operations in a staging environment before running them in production, especially for DROP PARTITION which is irreversible.
• When designing partition keys, prefer simple expressions that produce easily recognizable partition values.

Frequently Asked Questions

Q: What is the difference between PARTITION and PARTITION ID?
A: PARTITION expects a value that matches the partition key expression's type (e.g., a number for toYYYYMM()). PARTITION ID expects the string identifier shown in the partition_id column of system.parts. Using PARTITION ID avoids type-matching issues.

Q: Can I use a WHERE-style filter instead of specifying a partition value?
A: No. ALTER TABLE ... DROP PARTITION requires an exact partition value or partition ID. You cannot use arbitrary WHERE conditions. If you need to delete specific rows, use ALTER TABLE ... DELETE WHERE ... instead.

Q: My partition key is (toYYYYMM(date), region). How do I specify the partition value?
A: Use a tuple: ALTER TABLE your_table DROP PARTITION (202301, 'US'). The values must match the types of each element in the partition key tuple.