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

User · Answer

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.

User · Answer

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

User · Answer

Try run following alter table   ALTER TABLE public users  ALTER COLUMN  password  TYPE varchar 300   USING  password   varchar

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to sql

Examples related to postgresql

Examples related to varchar

Examples related to alter-table