Resize a column in a PostgreSQL table without changing data

- 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.


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


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


This can be verified by running \d DATABASENAME


comments powered by Disqus
rss facebook twitter github gitlab youtube mail spotify lastfm instagram linkedin google google-plus pinterest medium vimeo stackoverflow reddit quora quora