Re: [ADMIN] a problem - Mailing list pgsql-general

Robert Treat wrote:
>On Fri, 2003-08-22 at 05:01, sharvari N wrote:
> > hello
> > How do i change the definition of  a column? one of the columns width is
> > not sufficient to store the data. I want to change the width. how to do
> > that in postgres? I tried doing alter table + change/modify. both of
> > them doesn't work in postgres.
>
>you have to hack the system tables for this, though i can't seem to
>recall the exact field name this morning.  the query is certainly in the
>archives as i've answered this one before, if you were too lazy to look
>it up i guess i will be too ;-)

OK, Sharvari, it was me who asked this same thing a couple of weeks ago, so
I'm transcribing here the fine advice from our guru Tom:

--------------------------------------------------
"Claudio Lapidus" <clapidus@hotmail.com> writes:
>I need to modify a column which is currently defined as varchar(30) to
>varchar(40). I can see from the docs (and trial) that I cannot directly
>alter a column this way, so I intend to do the following:

>ALTER TABLE t1 ADD COLUMN duplicate varchar(40);
>UPDATE t1 SET duplicate=original;
>ALTER TABLE t1 DROP COLUMN original;
>ALTER TABLE t1 RENAME duplicate TO original;

>But I'm worried about messing up things if I run this queries while the
>database is live, i.e. there are other processes writing to the table.

As you should be.

>if I enclose the above into a BEGIN/COMMIT pair, would you say it is safe
>to
>run concurrently with other transactions?

Yes, because the first ALTER will take an exclusive lock on table t1,
which will be held through the rest of the transaction.  So it will be
safe a fortiori.  However, if the table is large you may regret holding
an exclusive lock for all the time it takes to do that UPDATE.

Personally, being a database hacker, I would solve this problem with a
quick modification of the atttypmod field that expresses the column
length:

UPDATE pg_attribute SET atttypmod = 40 + 4 -- +4 for varchar overhead
WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = 't1')
AND attname = 'original';

Since you are increasing the length limit, and it's varchar not char,
there is nothing that need be done to the data itself, so this is
sufficient.

I would strongly recommend practicing on a scratch database until you
are sure you've got the hang of this ;-).  Also you might want to do a
BEGIN first, and not COMMIT until you're sure \d display of the table
looks right.

            regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

_________________________________________________________________
The new MSN 8: advanced junk mail protection and 2 months FREE*
http://join.msn.com/?page=features/junkmail