I am using PostgreSQL database.
I have a table with millions of rows. I have a
varchar column with 2000 size and i want to make it to 4000. I ran the
alter table command, but it is taking too much time.
Is there any quicker way to do it?
Yes, there is a quicker way to do it if you are using version 9.1. The PostgreSQL version 9.1 release notes include this:
Further improvements in this area are coming in version 9.2, which hits beta release next week. But for now it appears the faster way is to alter the type to
|show 2 more comments|
You could always dump the database, edit the dumped schema, and import it back. A lot faster than altering the table. If you can't do it, here are some things you can do to make it faster.
Note that clustering and vacuuming can take a long time. Vacuuming does not read-lock the database, but it may not affect the speed of altering the table.
By the way, you should define the attribute as plain VARCHAR, without size limit. Postgres handles the case just as effectively as VARCHAR(4000), but you don't have to resize it later.
There is really hardly any good reason to use the data type
If you need to enforce a maximum length, add a check constraint like this:
You can drop and recreate this constraint with a different maximum length without re-writing the table.
Start by converting to
In the case of increasing the limit , some people would certainly prefer using the unsupported hack mentioned here:
Advantage: immediate, no rewriting of the table.
Drawback: not supported, so you use it at your own peril.
Generally speaking, no. The reason that it takes so long for millions of records is that it is an intense write operation on the disk. Each row has to take up more space, and with the way that databases actually write data to disk that means moving a lot of other data around. If you have indexes this can be even more intense.
As suggested in comments, if you do not have triggers or constraints on the table, you can copy to a new, properly configured table. Or you could add an additional column, and copy to that. However, for a table with millions of rows, you're talking about moving at a minimum 2 billion bytes, which is not a trivial amount of movement. It's going to take time. How long have you let it run?