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.
ALTER TABLE ...ALTER COLUMN...TYPE... command is useful if we want to alter the data somehow, 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 reasons). We can now safely change this to a
varchar(1000) size by issuing 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 reasons inside PG.
You can again check the size with the first query above.