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

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.

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.

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

.....

)

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.

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.

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

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

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;

Try run following alter table:

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