Resize a column in a PostgreSQL table without changing data

Wednesday. August 14, 2019 - 1 min

I needed to increase the length of a column without any change in data. I went through stackoverflow as usual :) and found a old article by sniptools .

The original article was writen in 2009 and few things has to be changed.

TL;DR

In the below example table name is TAB1 and the column that we want to alter is COL1

To check the existing size, simply run the following query.

SELECT atttypmod FROM pg_catalog.pg_attribute
WHERE attrelid = 'TAB1'::regclass
AND attname = 'COL1';

The sample result is

attypmod
---------
34

This means that the column current size is 30 (4 was added for a legacy reason it seems.)

Now we can change it to varchar(40) by executing the following query.

UPDATE pg_catalog.pg_attribute SET atttypmod = 40+4
WHERE attrelid = 'TAB1'::regclass
AND attname = 'COL1';

The sample result is

UPDATE 1

This can be verified by running \d DATABASENAME

Cheers!!!