Forum OpenACS Q&A: Increase column size

Collapse
1: Increase column size
Posted by Peter Lim on 11/22/02 11:50 PM

OK, I know that I'm probably just being blind or ignorant about this, but I can't figure out how to increase the column width of the body column of the news_items table, while retaining all of the information. I think that it probably involves recreating the table, copying the data to the new table, and then dropping the original table, but my brain just isn't working this Friday afternoon. (Or is there another way to do it?) (I know that I've done this before, but I can't remember how I did it.)

So, can anyone help?

I'm using OpenACS 3.2.5, with Postgresql-7.1.2

Peter

Collapse
2: Re: Increase column size (response to 1)
Posted by Bart Teeuwisse on 11/23/02 12:01 AM
Peter,

Postgres offers no 'alter table' statement to increase the column size of a varchar. However you can manipulate the system tables directly. The size of a varchar is stored in pg_attribute as the actual size + 4.

Here is an example of how I recently increased the size of a custom column called headerstuff in the ec_categories table to 500 characters:

update pg_attribute set atttypmod = 504 
where attrelid = ( select oid from pg_class where relname = 'ec_categories' ) 
and attname = 'headerstuff';

/Bart

Collapse
3: Re: Increase column size (response to 1)
Posted by Peter Lim on 11/23/02 12:24 AM

Thank you!

I did figure out that Postgresql doesn't have an ALTER COLUMN command that would do what I wanted, but I couldn't figure out how to change the column size any other way than recreating and dropping the table, but your code is so much easier.

I had a vague recollection of seeing a previous posting that did the same thing, but I couldn't find it.

Again, thanks! (Especially for the quick response!)

Peter

Collapse
4: Re: Increase column size (response to 1)
Posted by Roberto Mello on 11/23/02 12:28 AM
I've used the data dictionary trick Bartt described, and it works. I was a little concerned that it could affect my database somehow, but things seems to be working ok 😊

Unfortunately, this functionality won't be in the first 7.3 release, but I've heard it will be in 7.3.1.

Collapse
5: Re: Increase column size (response to 1)
Posted by chandra shekher gupta on 09/19/06 10:42 AM
Hi Folks,

I say many-many thnaks to you people.
i have also one database on production and i am using postgres 8.1. I have to increase Option2 column size.This has helped me a lot ...Thanks a lot again.

update pg_attribute set atttypmod = 1000
where attrelid = ( select oid from pg_class where relname = 'questionaire_instance' )
and attname = 'option2';

Regards
Chandra shekher gupta
Infoaxon Technology UK limited.