Thread: Re: [pgadmin-hackers] Fwd: Re: Howto change column

At 14:46 07/11/01 +0000, you wrote:
> > >If you're willing to do a little magic to the system tables (and you
> > >have a recent backup :) ).  You can change the atttypmod of
> > the column
> > >in question from 14 to 24.  This really only works on
> > variable length
> > >items and only to expand them, but...
> > >
> > >You pretty much need to do a sequence like:
> > >select oid, relname from pg_class where
> > relname='<tablename>'; update
> > >pg_attribute set atttypmod=24 where attrelid=<oid from previous>
> > >  and attname='<attributename>'
> > >in a superuser account.
>
>Though technically correct, that sounds like a recipe for disaster! Of
>course, the atttypmod doesn't always relate directly to the length of the
>column, and even when it does, it's usually length+4
>
>Regards, Dave.

On Wed, 7 Nov 2001, Jean-Michel POURE wrote:

> At 14:46 07/11/01 +0000, you wrote:
> > > >If you're willing to do a little magic to the system tables (and you
> > > >have a recent backup :) ).  You can change the atttypmod of
> > > the column
> > > >in question from 14 to 24.  This really only works on
> > > variable length
> > > >items and only to expand them, but...
> > > >
> > > >You pretty much need to do a sequence like:
> > > >select oid, relname from pg_class where
> > > relname='<tablename>'; update
> > > >pg_attribute set atttypmod=24 where attrelid=<oid from previous>
> > > >  and attname='<attributename>'
> > > >in a superuser account.
> >
> >Though technically correct, that sounds like a recipe for disaster! Of
> >course, the atttypmod doesn't always relate directly to the length of the
> >column, and even when it does, it's usually length+4

For the text types I think it's generally length+4.  For numeric, I think
it's precision*65536+scale.  The best way to find out is probably to
declare a column of the target type and check.

Of course, the best way to deal with this right now is to dump the
affected tables and reload until drop field is done.

Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> For the text types I think it's generally length+4.  For numeric, I think
> it's precision*65536+scale.  The best way to find out is probably to
> declare a column of the target type and check.

Or experiment with format_type(), or read the source code for same.
For example:

regression=# select oid from pg_type where typname = 'numeric';
 oid
------
 1700
(1 row)

regression=# select format_type(1700, 10 * 65536 + 7);
  format_type
---------------
 numeric(10,3)
(1 row)

Looks like there's an offset of 4 for numeric, too.  But a look into
src/backend/utils/adt/format_type.c gives the exact recipe ...

            regards, tom lane