Обсуждение: 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 -------------------------------------------------------------------------- Nick Fankhauser nickf@ontko.com Phone 1.765.935.4283 Fax 1.765.962.9788 Ray Ontko & Co. Software Consulting Services http://www.ontko.com/
"Nick Fankhauser" <nickf@ontko.com> writes: > 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
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
Tom, Joe: > Yup, that's the standard hack. Thanks very much! This saved us hours. -Nick