Re: Alter the column data type of the large data volume table. - Mailing list pgsql-general

From Ron
Subject Re: Alter the column data type of the large data volume table.
Date
Msg-id ece5a674-1af9-0e4b-1dcc-41f0b3d92d53@gmail.com
Whole thread Raw
In response to Re: Alter the column data type of the large data volume table.  (Michael Lewis <mlewis@entrata.com>)
List pgsql-general
On 12/3/20 11:53 AM, Michael Lewis wrote:


On Thu, Dec 3, 2020 at 10:18 AM Rich Shepard <rshepard@appl-ecosys.com> wrote:
Tell me, please, why

ALTER TABLE <tablename> ALTER COLUMN <columnname> SET DATA TYPE BIGINT

will not do the job?

I've found some varchar columns in a couple of tables too small and used the
above to increase their size. Worked perfectly.


Something else noteworthy is that with varchar, there is no rewrite of the table. You are just removing or loosening the length restriction on a variable width column type. I believe you could change all columns from VARCHAR(n) to TEXT or VARCHAR(n+X) and have it take almost no time at all since you are only impacting the catalog tables (pretty sure about that at least). With a fixed width column like int4 to int8, all the rows need to be actually re-written.

And in this case it's the PK, so indexed and thus even slower.  Lots slowe.

--
Angular momentum makes the world go 'round.

pgsql-general by date:

Previous
From: Michael Lewis
Date:
Subject: Re: Alter the column data type of the large data volume table.
Next
From: Michael Paquier
Date:
Subject: Re: postgres-10 with FIPS