Safely increase size of a column in a PostgreSQL table without changing data

Sometimes we find that the size of a column is not sufficient to hold the data we need to store. We need to safely increase size of a column in a PostgreSQL table without changing data.

Say, we have default varchar(255) and we wish to make varchar(1000). We don’t want to change the data in the existing records and just increase the size from 255 to 1000.

The ALTER TABLE ...ALTER COLUMN...TYPE... com­mand is use­ful if we want to alter the data some­how, or change the data type. We have different ways to increase the size of the column, I use the following way to do it.

Let us say, we have a table “MY_TABLE” with column “MY_COLUMN” which has initial size of 255 and we want to increase its size to 1000.

First of all, let us check the initial size with following query:

SELECT atttypmod FROM pg_attribute
WHERE attrelid = 'MY_TABLE'::regclass
AND attname = 'MY_COLUMN';
 
atttypmod
-----------
259
(1 ROW)

This shows that the size is 259 (4 is added for legacy rea­sons). We can now safely change this to a varchar(1000) size by issu­ing this query:

UPDATE pg_attribute SET atttypmod = 1000+4
WHERE attrelid = 'MY_TABLE'::regclass
AND attname = 'MY_COLUMN';
 
UPDATE 1

I added the 4 to the size of 1000, for some legacy rea­sons inside PG.

Done.

You can again check the size with the first query above.

Thank you.

I am Manohar Bhattarai, a vivid java programmer. I am a keen Linux user. I currently boot Ubuntu Linux. I am a Free/Libre Open Source Software enthusiast.

Posted in Postgres Tagged with: , , , , , , , , , ,

Leave a Reply