How to Alter Column Size in PostgreSQL without modificating content

Hello everyone,

Today I'll give you an useful tip of how to alter the size of a varchar column hacking the database catalog data.

First of all, you have to know that it's very dangerous, and the official method to do this is by using an ALTER TABLE statement. But you know it's difficult to do that if the table you are trying to modify already has records in it. That's why I used this method and has always worked like a charm.

WITH RECURSIVE vlist AS (
SELECT c.oid::REGCLASS AS view_name
FROM pg_class c
WHERE c.relname = 'm_product' /* TABLENAME */
UNION ALL
SELECT DISTINCT r.ev_class::REGCLASS AS view_name
FROM pg_depend d
JOIN pg_rewrite r ON (r.oid = d.objid)
JOIN vlist ON (vlist.view_name = d.refobjid)
WHERE d.refobjsubid != 0
)
UPDATE pg_attribute
SET atttypmod = 2000 + 4 /*REPLACE 2000 TO DESIRED LENGTH*/
FROM vlist
WHERE attrelid = vlist.view_name
AND attname = 'description'; /* COLUMNNAME */

Please try it out and leave the comments below, feel free to ask us anything about this or you can suggest some new topics.