public inbox for [email protected]  

help / color / mirror / Atom feed

From: Christophe Courtois <[email protected]>
To: Gambhir Singh <[email protected]>
To: Pgsql-admin <[email protected]>
Subject: Re: alter the datatype of Partition Key
Date: Mon, 16 Jun 2025 10:13:59 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAHOGQfUDcr+ZRJ=q+7uNgU+qFzRx6uuwbzHsfyXtFxd9PYAV9Q@mail.gmail.com>
References: <CAHOGQfUDcr+ZRJ=q+7uNgU+qFzRx6uuwbzHsfyXtFxd9PYAV9Q@mail.gmail.com>

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

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: alter the datatype of Partition Key
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox