Subject: alter the datatype of Partition Key

From: Gambhir Singh <[email-masked]>
Date: Mon, 16 Jun 2025 12:45:18 +0530

Hi,

We have a partition table with partitioning type range and got the
requirement from the application team to alter the datatype of partitioning
key from bigint to varchar. Please suggest to me the way how this can be
achieved.

Data volume is quite huge in the table. ~50 Billion rows

-- 
Thanks & Regards
Gambhir Singh

---

From: Christophe Courtois <[email-masked]>
Date: Mon, 16 Jun 2025 10:13:59 +0200

Hi,

Le 16/06/2025 à 09:15, Gambhir Singh a écrit :
> requirement from the application team to alter the datatype of 
> partitioning key from bigint to varchar.
 > Please suggest to me the way how this can be achieved.

You probably found that ALTER COLUMN will fail on a partitioned table :

ALTER TABLE pgbench_accounts ALTER COLUMN aid TYPE VARCHAR(16) ;
ERROR:  cannot alter column "aid" because it is part of the partition 
key of relation "pgbench_accounts"

(I don't see this limit in the doc BTW)

So I'm afraid that you will have to manually transfer the data from one 
table to the other, one way or another.

> Data volume is quite huge in the table. ~50 Billion rows

Anyway, changing the type if the table will require to completely 
rewrite the table.

You have a big table, you will have a long downtime.
Is the application team aware that this is not a trivial requirement ? 
Changing a key from int to varchar does not sound like a good idea.

To reduce the downtime, perhaps logical replication would help.
It's possible to  replicate from a table with a int PK to a table with a 
varchar PK, but I did not try with a partitioned table. This is a bit 
more complex to set up.

-- 
_________  ____
|         ||    |   Christophe Courtois
|         ||__  |   Consultant DALIBO
|         |   | |   43, rue du Faubourg Montmartre
|    -    |  / /    75009 Paris
|___| |___|  \/     www.dalibo.com

---

From: Gambhir Singh <[email-masked]>
Date: Mon, 16 Jun 2025 18:46:47 +0530

Please help me on this

Thanks & Regards
Gambhir Singh

---

From: akshay polji <[email-masked]>
Date: Thu, 19 Jun 2025 12:31:51 +0530

The cleanest way to do this would be to create a new table with varchar
datatype for the partition key along with the partitions and then move the
data partition-by-partition.
The rollback would be super easy as well in case some issues arise in
applications due to change of datatype.

Regards,
Akshay