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
