Postgresql - change the size of a varchar column to lower length

sql postgresql varchar alter-table
Labynocle picture Labynocle · Oct 11, 2011 · Viewed 253.2k times · Source

I have a question about the ALTER TABLE command on a really large table (almost 30 millions rows). One of its columns is a varchar(255) and I would like to resize it to a varchar(40). Basically, I would like to change my column by running the following command:

ALTER TABLE mytable ALTER COLUMN mycolumn TYPE varchar(40);

I have no problem if the process is very long but it seems my table is no more readable during the ALTER TABLE command. Is there a smarter way? Maybe add a new column, copy values from the old column, drop the old column and finally rename the new one?

Note: I use PostgreSQL 9.0.

Answer

sir_leslie picture sir_leslie · Feb 27, 2015

In PostgreSQL 9.1 there is an easier way

http://www.postgresql.org/message-id/[email protected]

CREATE TABLE foog(a varchar(10));

ALTER TABLE foog ALTER COLUMN a TYPE varchar(30);

postgres=# \d foog

 Table "public.foog"
 Column |         Type          | Modifiers
--------+-----------------------+-----------
 a      | character varying(30) |

Related questions

Change PostgreSQL columns used in views

I would like PostegreSQL to relax a bit. Every time I want to change a column used in a view, it seems I have to drop the view, change the field and then recreate the view. Can I waive the …

Read More
Select truncated string from Postgres

I have some large varchar values in Postgres that I want to SELECT and move somewhere else. The place they are going to uses VARCHAR(4095) so I only need at most 4095 bytes (I think that's bytes) and some of these …

Read More
Select first row in each GROUP BY group?

As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY. Specifically, if I've got a purchases table that looks like this: SELECT * FROM purchases; My Output: id | customer | total …

Read More