public inbox for pgsql-admin@postgresql.org  

help / color / mirror / Atom feed

From: Adrian Klaver <adrian.klaver@aklaver.com>
To: Gambhir Singh <gambhir.singh05@gmail.com>
Cc: pgsql-general <pgsql-general@postgresql.org>
Subject: Re: Alter the datatype on all tables present in the database (bigint to varchar)
Date: Thu, 14 May 2026 13:11:45 -0700
Message-ID: <fdcc86e6-19bb-4816-b343-d0dfdcead13b@aklaver.com> (raw)
In-Reply-To: <CAHOGQfXp6f+=ZYQDymmCyu07h9j4OMrHVRBram5Y=oRWCdVHWw@mail.gmail.com>
References: <CAHOGQfVo0FWX8mS9FLTmpDOb5XC5-T4qx4ouhw0wOfNeLdahsA@mail.gmail.com>
	<dc1b5e17-92ec-4f27-84f8-bbb317bdc2a9@aklaver.com>
	<CAHOGQfXp6f+=ZYQDymmCyu07h9j4OMrHVRBram5Y=oRWCdVHWw@mail.gmail.com>

On 5/14/26 11:34 AM, Gambhir Singh wrote:

Please reply to list also.
Ccing list

> Hi Adrian,
> 
> I've received this requirement from the application team. My main 
> concern is the partitioned tables.

I would think there would be a least some rationale for doing this, if 
for no other reason then to determine whether this is the best solution.


 From here:

https://www.postgresql.org/docs/17/sql-altertable.html

"Adding a column with a volatile DEFAULT or changing the type of an 
existing column will require the entire table and its indexes to be 
rewritten. As an exception, when changing the type of an existing 
column, if the USING clause does not change the column contents and the 
old type is either binary coercible to the new type or an unconstrained 
domain over the new type, a table rewrite is not needed. However, 
indexes must always be rebuilt unless the system can verify that the new 
index would be logically equivalent to the existing one. For example, if 
the collation for a column has been changed, an index rebuild is always 
required because the new sort order might be different. However, in the 
absence of a collation change, a column can be changed from text to 
varchar (or vice versa) without rebuilding the indexes because these 
data types sort identically. Table and/or index rebuilds may take a 
significant amount of time for a large table; and will temporarily 
require as much as double the disk space."


You also mentioned FKs, do these involve the columns being changed?

> 
> Thanks & Regards
> Gambhir Singh
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.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: pgsql-admin@postgresql.org
  Cc: adrian.klaver@aklaver.com, gambhir.singh05@gmail.com, pgsql-general@postgresql.org
  Subject: Re: Alter the datatype on all tables present in the database (bigint to varchar)
  In-Reply-To: <fdcc86e6-19bb-4816-b343-d0dfdcead13b@aklaver.com>

* 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