Thread: Length of Varchar
Hi all, i have a little question on changing the length of a varchar field. Is there another way than dump and reload if i want to change the length of a varchar field ? I have search the idocs and found some docs about the system-table pg_attribute, where the length of a varchar-field is stored in atttypmod. Is it possible to change the value of atttypmod and is it safe to change this value? Thanks in advance as
On Friday 24 January 2003 07:37, Andre Schubert wrote: > i have a little question on changing the length of a varchar field. > Is there another way than dump and reload if i want to change the length > of a varchar field ? in 7.3: BEGIN; ALTER TABLE foo RENAME your_field TO your_field_old; ALTER TABLE foo ADD COLUMN your_field VARCHAR([new length]); UPDATE foo SET your_field=your_field_old; ALTER TABLE foo DROP COLUMN your_field_old; COMMIT; In 7.3, if the new column is shorter you may need to truncate the values being inserted. In versions < 7.3 you will not be able to drop the old column; there you may want to recreate the table, there's a techdoc article here: http://techdocs.postgresql.org/techdocs/updatingcolumns.php > I have search the idocs and found some docs about the system-table > pg_attribute, where the length of a varchar-field is stored in atttypmod. > Is it possible to change the value of atttypmod and is it safe to change > this value? Possible but probably not safe. No doubt someone will be along shortly with a more accurate opinion ;-). Ian Barwick barwick@gmx.net
On Friday 24 January 2003 09:26, Ian Barwick wrote: > On Friday 24 January 2003 07:37, Andre Schubert wrote: > > i have a little question on changing the length of a varchar field. > > Is there another way than dump and reload if i want to change the length > > of a varchar field ? > > in 7.3: > > BEGIN; > ALTER TABLE foo RENAME your_field TO your_field_old; > ALTER TABLE foo ADD COLUMN your_field VARCHAR([new length]); > UPDATE foo SET your_field=your_field_old; > ALTER TABLE foo DROP COLUMN your_field_old; > COMMIT; > > In 7.3, if the new column is shorter you may need to truncate the values > being inserted. > > In versions < 7.3 you will not be able to drop the old column; there you > may want to recreate the table, there's a techdoc article here: > http://techdocs.postgresql.org/techdocs/updatingcolumns.php Addendum: Someone has written me a private email pointing out that if you do recreate a table like this, dependent views etc. will no longer work, and asks if there is any way around this apart from upgrading to 7.3. I think the short answer is "no". (As in there's "no such thing as a free lunch" ;-) Ian Barwick barwick@gmx.net
On Fri, 2003-01-24 at 05:33, Ian Barwick wrote: > On Friday 24 January 2003 09:26, Ian Barwick wrote: > > On Friday 24 January 2003 07:37, Andre Schubert wrote: > > > i have a little question on changing the length of a varchar field. > > > Is there another way than dump and reload if i want to change the length > > > of a varchar field ? > > > > in 7.3: > > > > BEGIN; > > ALTER TABLE foo RENAME your_field TO your_field_old; > > ALTER TABLE foo ADD COLUMN your_field VARCHAR([new length]); > > UPDATE foo SET your_field=your_field_old; > > ALTER TABLE foo DROP COLUMN your_field_old; > > COMMIT; > > > > In 7.3, if the new column is shorter you may need to truncate the values > > being inserted. > > > > In versions < 7.3 you will not be able to drop the old column; there you > > may want to recreate the table, there's a techdoc article here: > > http://techdocs.postgresql.org/techdocs/updatingcolumns.php > > Addendum: > Someone has written me a private email pointing out that if you do > recreate a table like this, dependent views etc. will no longer work, > and asks if there is any way around this apart from upgrading > to 7.3. I think the short answer is "no". (As in there's "no such thing > as a free lunch" ;-) > There is a "gamblers lunch", for those willing to risk total system failure and the lives of there first born child. You can issue the following query to find out the attribute information for the field you want to modify select a.* from pg_attribute a, pg_class c where c.relname='tablename' and a.attnum > 0 and a.attrelid = c.oid; if you then do: update pg_attribute set atttypmod=N where attrelid=(select oid from pg_class where relname='tablename') and attname='fieldname'; where N = length of desired field + 4. you can then run the first query to verify the changes. Robert Treat
Robert Treat <xzilla@users.sourceforge.net> writes:
> There is a "gamblers lunch", for those willing to risk total system
> failure and the lives of there first born child.
Aw, it's not that dangerous ;-). You can improve your odds considerably
if you issue BEGIN; before you start dorking with the system tables,
and COMMIT only after you're satisfied you have things right. If you
realize you blew it, ROLLBACK.
Still, I'd recommend practicing on a scratch database before you do it
for real.
regards, tom lane
Also, it only really works well for lengthening columns. If you shorten them, the old values will remain their original longer length. --------------------------------------------------------------------------- Tom Lane wrote: > Robert Treat <xzilla@users.sourceforge.net> writes: > > There is a "gamblers lunch", for those willing to risk total system > > failure and the lives of there first born child. > > Aw, it's not that dangerous ;-). You can improve your odds considerably > if you issue BEGIN; before you start dorking with the system tables, > and COMMIT only after you're satisfied you have things right. If you > realize you blew it, ROLLBACK. > > Still, I'd recommend practicing on a scratch database before you do it > for real. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073