Re: ALTER TEXT field to VARCHAR(1024)

On 19/09/14 19:32, Marius Grama 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 am curious as to why you want to change text to VARCHAR(1024),
especially as I think that the middleware should be controlling how long
a string is saved in the database rather than end user client code (for
several reasons., including security concerns). However, I do not know
your use cases, nor your overall situation - so my concerns may not
apply to you.

Cheers,
Gavin