Alter definition of a column

From: af300wsm(at)gmail(dot)com
To: pgsql-general(at)postgresql(dot)org
Subject: Alter definition of a column
Date: 2007-01-17 20:09:16
Message-ID: 1169064556.219745.8920@m58g2000cwm.googlegroups.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Stefan Kaltenbrunner 2007-01-17 20:12:43 Re: Index bloat of 4x
Previous Message Ron Mayer 2007-01-17 19:47:55 Re: [GENERAL] Autovacuum Improvements