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

Question

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 1

In PostgreSQL 9.1 there is an easier way

http://www.postgresql.org/message-id/162867790801110710g3c686010qcdd852e721e7a559@mail.gmail.com

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

Answer 2

There's a description of how to do this at Resize a column in a PostgreSQL table without changing data. You have to hack the database catalog data. The only way to do this officially is with ALTER TABLE, and as you've noted that change will lock and rewrite the entire table while it's running.

Make sure you read the Character Types section of the docs before changing this. All sorts of weird cases to be aware of here. The length check is done when values are stored into the rows. If you hack a lower limit in there, that will not reduce the size of existing values at all. You would be wise to do a scan over the whole table looking for rows where the length of the field is >40 characters after making the change. You'll need to figure out how to truncate those manually--so you're back some locks just on oversize ones--because if someone tries to update anything on that row it's going to reject it as too big now, at the point it goes to store the new version of the row. Hilarity ensues for the user.

VARCHAR is a terrible type that exists in PostgreSQL only to comply with its associated terrible part of the SQL standard. If you don't care about multi-database compatibility, consider storing your data as TEXT and add a constraint to limits its length. Constraints you can change around without this table lock/rewrite problem, and they can do more integrity checking than just the weak length check.

Answer 3

Ok, I'm probably late to the party, BUT...

THERE'S NO NEED TO RESIZE THE COLUMN IN YOUR CASE!

Postgres, unlike some other databases, is smart enough to only use just enough space to fit the string (even using compression for longer strings), so even if your column is declared as VARCHAR(255) - if you store 40-character strings in the column, the space usage will be 40 bytes + 1 byte of overhead.

The storage requirement for a short string (up to 126 bytes) is 1 byte plus the actual string, which includes the space padding in the case of character. Longer strings have 4 bytes of overhead instead of 1. Long strings are compressed by the system automatically, so the physical requirement on disk might be less. Very long values are also stored in background tables so that they do not interfere with rapid access to shorter column values.

(http://www.postgresql.org/docs/9.0/interactive/datatype-character.html)

The size specification in VARCHAR is only used to check the size of the values which are inserted, it does not affect the disk layout. In fact, VARCHAR and TEXT fields are stored in the same way in Postgres.

Answer 4

I was facing the same problem trying to truncate a VARCHAR from 32 to 8 and getting the ERROR: value too long for type character varying(8). I want to stay as close to SQL as possible because I'm using a self-made JPA-like structure that we might have to switch to different DBMS according to customer's choices (PostgreSQL being the default one). Hence, I don't want to use the trick of altering System tables.

I ended using the USING statement in the ALTER TABLE:

ALTER TABLE "MY_TABLE" ALTER COLUMN "MyColumn" TYPE varchar(8)
USING substr("MyColumn", 1, 8)

As @raylu noted, ALTER acquires an exclusive lock on the table so all other operations will be delayed until it completes.

Answer 5

if you put the alter into a transaction the table should not be locked:

BEGIN;
  ALTER TABLE "public"."mytable" ALTER COLUMN "mycolumn" TYPE varchar(40);
COMMIT;

this worked for me blazing fast, few seconds on a table with more than 400k rows.

Answer 6

Adding new column and replacing new one with old worked for me, on redshift postgresql, refer this link for more details https://gist.github.com/mmasashi/7107430

BEGIN;
LOCK users;
ALTER TABLE users ADD COLUMN name_new varchar(512) DEFAULT NULL;
UPDATE users SET name_new = name;
ALTER TABLE users DROP name;
ALTER TABLE users RENAME name_new TO name;
END;

Answer 7

Here's the cache of the page described by Greg Smith. In case that dies as well, the alter statement looks like this:

UPDATE pg_attribute SET atttypmod = 35+4
WHERE attrelid = 'TABLE1'::regclass
AND attname = 'COL1';

Where your table is TABLE1, the column is COL1 and you want to set it to 35 characters (the +4 is needed for legacy purposes according to the link, possibly the overhead referred to by A.H. in the comments).

Answer 8

Try run following alter table:

ALTER TABLE public.users 
ALTER COLUMN "password" TYPE varchar(300) 
USING "password"::varchar;

Answer 9

I have found a very easy way to change the size i.e. the annotation @Size(min = 1, max = 50) which is part of "import javax.validation.constraints" i.e. "import javax.validation.constraints.Size;"

@Size(min = 1, max = 50)
private String country;


when executing  this is hibernate you get in pgAdmin III 


CREATE TABLE address
(
.....
  country character varying(50),

.....

)