I'm using Citus 12.1 on top of PostgreSQL, and I have a distributed table with range partitioning.

When I try to update a row and change the value of the partition key, I get the following error:

ERROR:  modifying the partition value of rows is not allowed
SQL state: 0A000

This is surprising because in PostgreSQL 11+, partition key updates are allowed and PostgreSQL internally handles it by deleting and reinserting the row into the appropriate partition.

Here’s a simplified version of my setup:

CREATE TABLE contact_details (
    id BIGINT,
    user_id BIGINT,
    created_at DATE,
    ...
) PARTITION BY RANGE (created_at);

And I’m trying to do:

UPDATE contact_details
SET created_at = '2025-01-01'
WHERE id = 123;

This throws the above error.

• Is there any way to enable this kind of row movement in Citus?
• Or do I need to manually delete and reinsert the row?
• Is there a recommended workaround when working with partition keys in Citus?

Any guidance would be appreciated!

I tried to update the value of a partition key (created_at) in a distributed Citus table. I expected PostgreSQL (with Citus) to automatically move the row to the appropriate partition, as it does in native PostgreSQL 11+. However, the update failed with the error: ERROR: modifying the partition value of rows is not allowed.

I was hoping Citus would support automatic row movement between partitions, but that doesn't seem to be the case. Now I'm looking for the correct way to handle this scenario or a recommended workaround.

I'm using Citus 12.1 on top of PostgreSQL, and I have a distributed table with range partitioning.

When I try to update a row and change the value of the partition key, I get the following error:

ERROR:  modifying the partition value of rows is not allowed
SQL state: 0A000

This is surprising because in PostgreSQL 11+, partition key updates are allowed and PostgreSQL internally handles it by deleting and reinserting the row into the appropriate partition.

Here’s a simplified version of my setup:

CREATE TABLE contact_details (
    id BIGINT,
    user_id BIGINT,
    created_at DATE,
    ...
) PARTITION BY RANGE (created_at);

And I’m trying to do:

UPDATE contact_details
SET created_at = '2025-01-01'
WHERE id = 123;

This throws the above error.

• Is there any way to enable this kind of row movement in Citus?
• Or do I need to manually delete and reinsert the row?
• Is there a recommended workaround when working with partition keys in Citus?

Any guidance would be appreciated!

I tried to update the value of a partition key (created_at) in a distributed Citus table. I expected PostgreSQL (with Citus) to automatically move the row to the appropriate partition, as it does in native PostgreSQL 11+. However, the update failed with the error: ERROR: modifying the partition value of rows is not allowed.

I was hoping Citus would support automatic row movement between partitions, but that doesn't seem to be the case. Now I'm looking for the correct way to handle this scenario or a recommended workaround.

• citus

1 Answer 1

The error you are getting is referring to the Citus partition column of the distributed partitioned table, i.e., the distribution column. In Citus, you cannot modify the value of the distribution column. The name partition column in Citus can be a bit confusing in this case, so better to address it as distribution column. Think of your error like this:

ERROR:  modifying the value of the distribution column of rows is not allowed

• Is there any way to enable this kind of row movement in Citus?

• Or do I need to manually delete and reinsert the row?

There is no way to modify the value of the distribution column in Citus with an UPDATE statement. Your second question is the only way to modify the value of the distribution column in Citus.

• Is there a recommended workaround when working with partition keys in Citus?

Your table contact_details is partitioned by range on the created_at column and judging by the error you are facing during UPDATE, your table is also distributed on the created_at column. The workaround, and the recommended practice, is to actually distribute the table on the id column. This way, it will work, and moreover Citus will route your UPDATE query to the relevant shard because that query filters on the id column, and Citus will execute it effectively.