Re: ALTER TEXT field to VARCHAR(1024)

On Fri, 19 Sep 2014 09:32:09 +0200
Marius Grama <mariusneo(at)gmail(dot)com> wrote:

> Hello,
>
>
> i am using Postgres 9.2 and I'd like to perform the following ALTER
> statement on a database table with about 30M entries :
>
> ALTER TABLE images ALTER COLUMN mask_descriptors TYPE VARCHAR(1024);
>
>
>
> The mask_descriptors field is currently having the type TEXT.
> I want to perform the ALTER due to the fact that it seems that copying the
> contents of the table to a BI SQL Server is done in row by row (instead of
> batch) when handling CLOBs.
>
> From the Postgres documentation I got the following :
>
> http://www.postgresql.org/docs/8.3/static/datatype-character.html
>
> Tip: There are no performance differences between these three types, apart
> from increased storage size when using the blank-padded type, and a few
> extra cycles to check the length when storing into a length-constrained
> column. While character has performance advantages in some other database
> systems, it has no such advantages in PostgreSQL. In most situations text
> or character varying should be used instead.
>
>
>
> Can anybody explain me what happens in the background when the alter
> statement is executed? I've tried it out on a small copy of the table (70K)
> and the operation completed in 0.2 seconds.
> Will the table be completely locked during the execution of the ALTER
> statement?

I share Gavin's concern that you're fixing this in the wrong place. I expect
that you'll be better served by configuring the middleware to do the right thing.

However, a more direct answer to your question: VARCHAR and TEXT are _the_same_
internally. Thus:

ALTER TABLE images ALTER COLUMN mask_descriptors TYPE VARCHAR;

would do nothing more than change the table definition. There is no need for
that statement to touch any data.

However, adding the length constraint of (1024) will force Postgres to check
every single value to ensure it complies with the constraint. I believe if
any row is longer than 1024 it will throw an error and abort the entire ATLER.

--
Bill Moran
I need your help to succeed:
http://gamesbybill.com