Note: Recommended not to tamper pg_catalogs.
On forum, I saw an interesting posting and also the solution, however few things of that solution made me to test it. Scenario is, “How to resize the VARCHAR column on a large table with less time and what are best approach’s”. As known standard way is to, Create a NEW column with desired size, Copy OLD data to newly created column, Drop the OLD column and finally rename the NEW with OLD column name. Be noted that am talking here about 100 million rows 🙂
Another approach is to modify PostgreSQL pg_catalog’s with new SIZE in the pg_attribute relation. Below are the steps.
- Drop if you have indexes on the RESIZE column
- Make the database into READ-ONLY mode (PG 9.x)
- Use UPDATE command on the pg_attribute relation on the column atttypmod(column size) and attname (column Name)
Command:
update pg_attribute set atttypmod = atttypmod + (desired Resize) where attrelid=<relation OID> and attname='<column Name>’;
Above command will update the pg_attribute relation with new column SIZE and allow you to insert the data according to new size. Here the table data not reformed with new SIZE instead its been overlooked by pg_catalogs changes.
Disadvantage:
You cannot decrease the size, if you do, then VARCHAR column size becomes ZERO and wont allow you to enter any data into the table. You get below error
ERROR: value too long for type character(0)
Will be back with more stuff. All the best :).
-Raghav