Re: Alter definition of a column

Hello,

The +4 is for the overhead of a varchar field.

Using ALTER TABLE <table> ALTER <field> TYPE VARCHAR(<newsize>) to
change the size requires scanning the entire table. For large tables,
this will be much slower than the pg_attribute query. Both will get
the job done.

af300wsm(at)gmail(dot)com wrote:
> Hello,
>
> In this case, I don't have to alter the definition to the extent of
> changing the data type, but rather to change the length of a varchar
> field/column. I found the following from a past posting to one of the
> PostgreSQL user lists that does work, but I'd like something a bit more
> elegant and wanted to find out if anything better exists in PostgreSQL
> 8.1.3 before I went and altered more than my test DB. Here is what I
> found (note that the person he was responding to wanted to bring his
> varchar field from 10 to 75 characters, in light of this, why is 4
> added to 75?):
>
> update pg_attribute set atttypmod = 75 + 4
> where attname = 'columnname' and
> attrelid = (select oid from pg_class where relname = 'tablename');
>
> The above comes from a posting made in Oct. of 2001. Also, I found
> this posting to this newsgroup in Oct. of last year mentioning:
>
> <quote>
> > Am I right in thinking that altering a column from varchar(n) to
> > varchar(n+m) requires each tuple to be visited?
>
> Yes. Doing otherwise would require an unreasonable amount of
> data-type-specific knowledge hardwired into ALTER COLUMN TYPE.
> </quote>
>
> What is this talking about and how does it apply to what I need to do?
>
> Thanks,
> Andy