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