Re: alter the datatype of Partition Key

Hi,

Le 16/06/2025 à 09:15, Gambhir Singh a écrit :
> 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.

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