Re: Alter the column data type of the large data volume table.

On 12/3/20 11:53 AM, Michael Lewis wrote:
>
>
> On Thu, Dec 3, 2020 at 10:18 AM Rich Shepard <rshepard(at)appl-ecosys(dot)com
> <mailto:rshepard(at)appl-ecosys(dot)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.