a problem

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 ;-)

and what is the maximum size to which a postgres database can grow? I
have huge data to store.

real big, most likely limited by your OS. i think the largest reported
is like 4TB

Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

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

if your table is named foo and the field you want to chage is bar, then:

BEGIN;
ALTER TABLE foo RENAME bar TO bar_old;
ALTER TABLE foo ADD bar VARCHAR(<new_size>);
UPDATE foo SET bar=bar_old;
*if everything went ok then*
ALTER TABLE foo DROP bar_old;
COMMIT;

same goes for CHAR data type.

about limitations:
http://www.postgresql.org/users-lounge/limitations.html

On Fri, 2003-08-22 at 06:01, sharvari N wrote: