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

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