Re: Is it safe to increase pg_attribute.atttypmod ?

Nick Fankhauser wrote:
> Hi-
>
> We need to increase the length of a varchar field, and couldn't find
> a way to do it using "alter table". Since the alternative is copying
> & re-creating a very large table, we starting poking around under
> the hood & found that pg_attribute.atttypmod seems to be always
> equal to the length plus 4 in the case of varchar fields.

Yes, the first 4 bytes are for an integer which represents length, and
the rest is the data itself.

>
> We created a test table and altered the varchar length on a field by
> increasing pg_attribute.atttypmod, and the length seemed to change
> and act as expected.
>
> The update to change a varchar(10) field to varchar(40) looked like
> this:
>
> update pg_attribute set atttypmod = 44 where attrelid = ( select oid
> from pg_class where relname = 'test' ) and attname = 'oldtest' ;
>
> Is this a smart thing to do?
>
> Are there other hidden related bits of data that will come back to
> haunt us later?
>

IIRC, this has been discussed a number of times before on one or another
of the PostgreSQL lists. The short answer, I think, is that the
procedure you describe should work just fine (but I do recommend you
take a look at the past threads on the subject). You may need to be a
bit more careful if you were going the other direction, i.e. varchar(40)
to varchar(10).

There was some talk of embedding this knowledge in an ALTER TABLE ALTER
COLUMN command for 7.3, but I haven't seen anything on that recently.

HTH,

Joe