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.