Resize a column in a PostgreSQL table without changing data: Repost

This morning I realized that one of the columns in the table I have recently created is running out of space. It is varchar(10) and I want to make it varchar(20) and of course wanted to do without losing any data which got filled in the last couple of days. Though there is a standard way to changing the size and type of the col using ALTER commands which can become tricky sometimes with the data. So I was looking for an alternate way and I found an awesome post on how to do this in an easy way without disturbing data, this is an almost 10 yrs old post so reposting the commands in case they get lost. For detailed reading please visit the blog link given in references.

Command to check the current size of a given column:

SELECT atttypmod FROM pg_attribute WHERE attrelid = 'TABLE1'::regclass AND attname = 'COL1';

In my case, the present size is 10 and I want to increase it to 25, so the command to update column will be as below:

UPDATE pg_attribute SET atttypmod = 25 WHERE attrelid = 'TABLE1'::regclass AND attname = 'COL1';

I hope this info and re-post helps.

Original blog link:
https://sniptools.com/databases/resize-a-column-in-a-postgresql-table-without-changing-data/

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.