Help changing varchar field

AFAIK there's no easy way to do this. Essentially you'll need to create
a new field of varchar(15), copy the contents of varchar(6), drop
varchar(6), then rename varchar(15). I'd recommend doing it all in one
transaction.

Robert Treat

On Thu, Oct 17, 2002 at 10:33:52AM -0400, Robert Treat wrote:

AFAIK there's no easy way to do this. Essentially you'll need to create
a new field of varchar(15), copy the contents of varchar(6), drop
varchar(6), then rename varchar(15). I'd recommend doing it all in one
transaction.

You can also set atttypmod to 15+4 (right now it should be 6+4) to the
attribute in pg_attribute.

--
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
"La espina, desde que nace, ya pincha" (Proverbio africano)

I'm a postresql newbie, big time. I've been assigned the task of making
some simple changes to our dealer website. The PO Number field is the first
request, currently it excepts just 6 characters (do to the varchar(6) type).
Alvaro: how can this change be implemented? i can get to a postrgesql
command prompt.

Karl

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Alvaro Herrera
Munoz
Sent: Thursday, October 17, 2002 7:46 AM
To: Robert Treat
Cc: Karl Stubsjoen; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Help changing varchar field

On Thu, Oct 17, 2002 at 10:33:52AM -0400, Robert Treat wrote:

AFAIK there's no easy way to do this. Essentially you'll need to create
a new field of varchar(15), copy the contents of varchar(6), drop
varchar(6), then rename varchar(15). I'd recommend doing it all in one
transaction.

You can also set atttypmod to 15+4 (right now it should be 6+4) to the
attribute in pg_attribute.

--
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
"La espina, desde que nace, ya pincha" (Proverbio africano)

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

I normally don't recommend mucking in system tables, but if you want to
do it that way this should work:

select a.* from pg_attribute a, pg_class c where c.relname='tablename'
and a.attnum > 0 and a.attrelid = c.oid;

this should show you the attribute information for the field you want to
modify.

then do

update pg_attribute set atttypmod=19 where attrelid=(select oid from
pg_class where relname='tablename') and attname='fieldname';

you can then run the first query to verify the changes

Robert Treat