Is there a way to create a temporary partition for an expression partitioned table? Currently, one of our month's partition data in a table requires recovery from the source so we're thinking of creating a temp partition to swap the new partition into with

ALTER TABLE {dbName}.{tableName}
    ADD TEMPORARY PARTITION tp202601 VALUES [("2026-01-01"), ("2026-02-01"));

But we get a

[42000][1064] Getting analyzing error. Detail message: Currently, you cannot manually add a single range partition to a table that is automatically partition.

This would be a SR 4.0 shared-data cluster. Ideally we'd like to be in a place to do a swap without having to totally drop the existing partition.

@Rocky - are there any ways to fix a partition’s worth of data in a single range expression partition?

ALTER TABLE {dbName}.{tableName}
    ADD TEMPORARY PARTITION tp202601 VALUES [("2026-01-01"), ("2026-02-01"));

But we get a

[42000][1064] Getting analyzing error. Detail message: Currently, you cannot manually add a single range partition to a table that is automatically partition.

This would be a SR 4.0 shared-data cluster. Ideally we'd like to be in a place to do a swap without having to totally drop the existing partition.

Hi there! Rocky here. I can certainly help you with that error. The error you're seeing occurs because your table is using Automatic Partitioning (also known as Expression Partitioning, e.g.,

PARTITION BY date_trunc(...)

or

PARTITION BY (column)

). In this mode, StarRocks manages the creation of partitions automatically based on the incoming data, and as the error message indicates, it restricts manual partition management operations like

ADD PARTITION

or

ADD TEMPORARY PARTITION

using the manual range syntax (

VALUES [...]

). For StarRocks 4.0, the recommended way to "fix" or replace a partition's worth of data in an automatically partitioned table is to use

INSERT OVERWRITE

combined with the

dynamic_overwrite

feature. The Solution:

INSERT OVERWRITE

with

dynamic_overwrite

INSERT OVERWRITE

performs an atomic swap internally. It loads the new data into a temporary location and then swaps it with the existing partition(s) once the load is successful. This means your table remains queryable and you don't have to manually drop partitions. Starting from StarRocks v3.4 (and fully supported in 4.0 shared-data), you can use the

dynamic_overwrite

property to ensure you only overwrite the specific partitions targeted by your query without affecting the rest of the table. How to do it:

sql
-- Enable dynamic overwrite so only affected partitions are replaced
SET dynamic_overwrite = true;

-- Overwrite the data for the specific range
INSERT OVERWRITE {tableName}
SELECT * 
FROM {sourceTable}
WHERE date_column >= '2026-01-01' AND date_column < '2026-02-01';

Or, you can use a hint if you prefer not to set a session variable:

sql
INSERT /*+ SET_VAR(dynamic_overwrite = true) */ OVERWRITE {tableName}
SELECT * 
FROM {sourceTable}
WHERE date_column >= '2026-01-01' AND date_column < '2026-02-01';

Why this is the best approach for SR 4.0: 1. Atomic Swap: It performs the swap exactly like a temporary partition

REPLACE

would, ensuring no downtime and no manual "drop" steps. 2. Compatibility: It is the native way to handle data replacement in tables where StarRocks manages the partitioning logic. 3. Safety: With

dynamic_overwrite = true

, StarRocks only touches the partitions that have matching data in your

SELECT

statement. If your query only returns data for January 2026, only the January 2026 partition(s) will be replaced. Note on Manual Range Partitioning If your workflow strictly requires manual control over specific range definitions (like using the

VALUES [("2026-01-01"), ("2026-02-01"))

syntax), you would need to define the table using Manual Range Partitioning instead of Automatic Partitioning. However, for most modern StarRocks use cases, sticking with Automatic Partitioning and using

INSERT OVERWRITE

is the more efficient and less error-prone path. References • table_design/data_distribution/expression_partitioning.md • administration/management/FE_configuration.md • table_design/data_distribution/list_partitioning.md • table_design/data_distribution/dynamic_partitioning.md