ALTER TEXT field to VARCHAR(1024)

From: Marius Grama <mariusneo(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: ALTER TEXT field to VARCHAR(1024)
Date: 2014-09-19 07:32:09
Message-ID: CAFYRqM+JTLEU=p3C1cW90Tskw-19dWkdtxJV0y6ETU4BB8w=8A@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Andres Freund 2014-09-19 07:33:57 Re: [GENERAL] pg_multixact issues
Previous Message Albe Laurenz 2014-09-19 07:19:48 Re: cloning database