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.

  • Pingback: Chris Fierer » Resize a column in a PostgreSQL table without temporary tables()

  • http://www.merishopping.com Farhan Ansari

    Very help­ful… thanks

  • Pingback: AquilaX’s development blog - Resize column in postgres without recreating()

  • http://www.leesmevoor.nl Ronald

    Super, thanx! It even works when the field is included in a view, and nor­mally you need to drop the view to alter the field.

  • http://www.goli.at googol

    ALTER TABLE auth_permission ALTER "name" TYPE char­ac­ter varying(255);

  • Ran­jit

    Good .
    But this can also be achived by below syntax

    ALTER TABLE ALTER COLUMN TYPE (new length);

  • Snip­tools

    googol, that will take a long time as it goes through the entire table — each and every row.

    The approach I sug­gest will be done in microseconds.

  • http://www.tanga.com Joe Van Dyk

    I believe 9.1 will let you do this with­out a table rewrite, but indexes will still need to be rebuilt.

  • http://www.facebook.com/dan.ancona Dan Ancona

    That is crazily awe­some. Just used it once and will def­i­nitely use it again! Thanks!

  • http://shanx.com/ Near­ly­Nor­mal

    Thanks Dan. Glad it's use­ful. I've used this in many a sit­u­a­tion — it really should be a much clearer part of the docs.

  • http://www.facebook.com/gunabalan.guna.12 Gun­a­balan Guna

    ALTER TABLE yourtable ALTER COLUMN col.name TYPE data type(length);

  • abdul

    how about the sql for undo­ing this? Imag­ine you are using a migra­tion tool where you need to seam­lessly migrate up/down..

  • http://www.plataformascout.com/ Mario Vial

    Gra­ci­aas!

  • disqus_y76mWBJaAd

    Very help­ful for me, thank alot

  • S Welsh

    Could I change 92 tables and views at once with a query?

  • Mar­cus

    A view on that table will still report the old col­umn length.

  • Erico Souza

    great man!

  • Mad­ha­van Kumar

    rockin baby…

  • vq

    Is there any way to drop length con­straint like this, with­out rewrit­ing tables/views?

  • vq

    got it, just set 0 as the size

  • Al Sam

    Does the update com­mand also include the "Update 1" at the end?

  • dlan­dis

    I was always curi­ous if this could be done with columns of type numeric(x,y). After play­ing around with it for a bit, I was able to fig­ure out a sim­i­lar hack. I wrote up a short blog post about it here: http://mrn-code.github.io/2016/09/14/Resizing-a-Numeric-Column-in-a-PostgreSQL-Table-without-Changing-Data/