You use PostgreSQL. You find that a column 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). Nothing else. I just want to change the size, keeping the data intact.
The ALTER TABLE ...ALTER COLUMN...TYPE... command is useful only if you want to alter the data somehow, or change the data type. Otherwise, it’ll be an aeon before this finishes even inside a transaction on a database of any meaningful size.
Until now, I was not familiar with any sensible mechanism to simply change the size in PG. But yesterday, Tom Lane himself suggested something ubercool in the list.
Let’s assume for the sake of simplicity that your table is called “TABLE1” and your column is “COL1″. You can find the size of your “COL1” column by issuing the following query on the system 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 reasons, we’re told). You can now conveniently change this to a varchar(35) size by issuing this command:
update pg_attribute set atttypmod = 35+4
where attrelid = 'TABLE1'::regclass
and attname = 'COL1';
UPDATE 1
Note that I manually added the 4 to the desired size of 35..again, for some legacy reasons inside PG. Done. That’s it. Should we check?
d TABLE1
Table "public.TABLE1"
Column | Type | Modifiers
--------+-----------------------+-----------
COL1 | character varying(35) |
Such a simple yet effective trick. Of course it’d be nicer if this is somehow included in a more proper way in the database, but this does the job.
3 Comments
[...] How to “Resize a column in a PostgreSQL table without changing data“. [...]
Very helpful… thanks
[...] A nice hack for postgres field size: http://sniptools.com/databases/resize-a-column-in-a-postgresql-table-without-changing-data [...]