Is this safe to perform on PostgreSQL 8.3.7 -> Resize a column in a PostgreSQL table without changing data

[Reid Thompson <reid.thompson@xxxxxxxx>]

reporting=# select version();
                                                 version
----------------------------------------------------------------------------------------------------------
 PostgreSQL 8.3.7 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) 
4.1.2 20071124 (Red Hat 4.1.2-42)
(1 row)

I've a parent table with several years of monthly partitioned children. 
 There has arisen a need to increase the max size of a couple of 
varchar fields.  Total size of these tables is approaching ~200 GB, with 
the larger monthly tables approximately 7-10GB each.

Would it be safe to use the below process to accomplish this?
Whether I use the below method, or the standard
  ALTER TABLE mytable ALTER COLUMN mycolumn TYPE varchar(new_size);
my assumption is that I should apply the change first to the child 
tables, then to the parent???

From
http://sniptools.com/databases/resize-a-column-in-a-postgresql-table-without-changing-data

Resize a column in a PostgreSQL table without changing data

You use Post­greSQL. You find that a col­umn you have in a table is of a 
smaller length than you now wish. In my case, this was a varchar(20) 
that I now wished to make varchar(35). Noth­ing else. I just want to 
change the size, keep­ing the data intact.

The ALTER TABLE ...ALTER COLUMN...TYPE... com­mand is use­ful only if 
you want to alter the data some­how, or change the data type. 
Oth­er­wise, it'll be an aeon before this fin­ishes even inside a 
trans­ac­tion on a data­base of any mean­ing­ful size.

Until now, I was not famil­iar with any sen­si­ble mech­a­nism to 
sim­ply change the size in PG. But yes­ter­day, Tom Lane him­self 
sug­gested some­thing uber­cool in the list.

Let's assume for the sake of sim­plic­ity that your table is called 
"TABLE1" and your col­umn is "COL1". You can find the size of your 
"COL1" col­umn by issu­ing the fol­low­ing query on the sys­tem tables:

SELECT atttypmod FROM pg_attribute
WHERE attrelid = 'TABLE1'::regclass
AND attname = 'COL1';

atttypmod
-----------
24
(1 ROW)

This means that the size is 20 (4 is added for legacy rea­sons, we're 
told). You can now con­ve­niently change this to a varchar(35) size by 
issu­ing this command:

UPDATE pg_attribute SET atttypmod = 35+4
WHERE attrelid = 'TABLE1'::regclass
AND attname = 'COL1';

UPDATE 1

Note that I man­u­ally added the 4 to the desired size of 35..again, for 
some legacy rea­sons inside PG. Done. That's it. Should we check?

d TABLE1

TABLE "public.TABLE1"
COLUMN  |  TYPE                 | Modifiers
--------+-----------------------+-----------
COL1    | CHARACTER VARYING(35) |

Such a sim­ple yet effec­tive trick. Of course it'd be nicer if this is 
some­how included in a more proper way in the data­base, but this does 
the job.

--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general