Is it safe to increase pg_attribute.atttypmod ? 
Author Message
 Is it safe to increase pg_attribute.atttypmod ?

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.

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?

-Nick

--------------------------------------------------------------------------

Ray Ontko & Co.     Software Consulting Services     http://www.***.com/

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.***.com/



Sat, 06 Nov 2004 06:26:06 GMT
 Is it safe to increase pg_attribute.atttypmod ?

Quote:

> 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?

Yup, that's the standard hack.

                        regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org



Sat, 06 Nov 2004 09:23:17 GMT
 Is it safe to increase pg_attribute.atttypmod ?
 > 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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org



Sat, 06 Nov 2004 09:29:19 GMT
 Is it safe to increase pg_attribute.atttypmod ?
Tom, Joe:

Quote:
> Yup, that's the standard hack.

Thanks very much! This saved us hours.

-Nick

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org



Sat, 06 Nov 2004 21:45:55 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. zpbit_in does not receive correct atttypmod

2. pgsql/doc FAQ TODO.detail/atttypmod TODO.detai ...

3. pgsql/doc/TODO.detail atttypmod

4. pgsql/src/include/catalog pg_attribute.h

5. pg_attribute

6. pg_attribute.attisinherited ?

7. pg_attribute always grow...

8. Altering a CHAR(4) column CHAR(5) changing pg_attribute

9. OIDs missing in pg_attribute?

10. pg_attribute.attisinherited ?

11. pgsql/src/include/catalog (catversion.h pg_attribute.h pg_class.h pg_database.h)

12. error ORA-01855: AM/A.M. or PM/P.M. required


 
Powered by phpBB® Forum Software