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)