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
