Thread: 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 Wed, 2002-10-16 at 17:06, Karl Stubsjoen wrote:
> Hello,  I have a varchar field (6).  I need to change it to varchar(15).
> How do I do this from a command line?
>
> Karl

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

On Thu, 2002-10-17 at 11:22, Karl Stubsjoen wrote:
> 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)
>