public inbox for [email protected]help / color / mirror / Atom feed
alter the datatype of Partition Key 4+ messages / 3 participants [nested] [flat]
* alter the datatype of Partition Key @ 2025-06-16 07:15 Gambhir Singh <[email protected]> 0 siblings, 2 replies; 4+ messages in thread From: Gambhir Singh @ 2025-06-16 07:15 UTC (permalink / raw) To: Pgsql-admin <[email protected]> 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 ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: alter the datatype of Partition Key @ 2025-06-16 08:13 Christophe Courtois <[email protected]> parent: Gambhir Singh <[email protected]> 1 sibling, 0 replies; 4+ messages in thread From: Christophe Courtois @ 2025-06-16 08:13 UTC (permalink / raw) To: Gambhir Singh <[email protected]>; Pgsql-admin <[email protected]> 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 ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: alter the datatype of Partition Key @ 2025-06-16 13:16 Gambhir Singh <[email protected]> parent: Gambhir Singh <[email protected]> 1 sibling, 1 reply; 4+ messages in thread From: Gambhir Singh @ 2025-06-16 13:16 UTC (permalink / raw) To: Pgsql-admin <[email protected]> Please help me on this Thanks & Regards Gambhir Singh On Mon, 16 Jun 2025 at 12:45, Gambhir Singh <[email protected]> wrote: > 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 > > ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: alter the datatype of Partition Key @ 2025-06-19 07:01 akshay polji <[email protected]> parent: Gambhir Singh <[email protected]> 0 siblings, 0 replies; 4+ messages in thread From: akshay polji @ 2025-06-19 07:01 UTC (permalink / raw) To: Gambhir Singh <[email protected]>; +Cc: Pgsql-admin <[email protected]> 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 On Mon, Jun 16, 2025 at 6:47 PM Gambhir Singh <[email protected]> wrote: > Please help me on this > > Thanks & Regards > Gambhir Singh > > > > On Mon, 16 Jun 2025 at 12:45, Gambhir Singh <[email protected]> > wrote: > >> 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 >> >> ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2025-06-19 07:01 UTC | newest] Thread overview: 4+ messages (download: mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-06-16 07:15 alter the datatype of Partition Key Gambhir Singh <[email protected]> 2025-06-16 08:13 ` Christophe Courtois <[email protected]> 2025-06-16 13:16 ` Gambhir Singh <[email protected]> 2025-06-19 07:01 ` akshay polji <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox