Thread: Changing the column length

From: "Michael Ryan S. Puncia" <mpuncia@census.gov.ph>

>             I am sorry that my question is out of line with this
> group(performance) but I need

-general might be more appropriate

>
> an urgent help :-( .pls .. I need to know how  to change the length of the
> column.

add a new column, use update to copy values from old column,
use alter table to rename columns

gnari

Michael,

>             I am sorry that my question is out of line with this
> group(performance) but I need
>
> an urgent help :-( .pls .. I need to know how  to change the length of the
> column.

In the future, try to provide more detail on your problem.    Fortunately, I
think I know what it is.

PostgreSQL does not support changing the length of VARCHAR columns in-place
until version 8.0 (currently in beta).   Instead, you need to:

1) Add a new column of the correct length;
2) Copy the data in the old column to the new column;
3) Drop the old column;
4) Rename the new column to the same name as the old column.

I realize that this approach can be quite painful if you have dependant views,
contstraints, etc.    It's why we fixed it for 8.0.  You can also:

1) pg_dump the database in text format;
2) edit the table definition in the pg_dump file;
3) re-load the database

While it *is* possible to change the column size by updating the system
tables, doing so is NOT recommended as it can cause unexpected database
errors.

--
Josh Berkus
Aglio Database Solutions
San Francisco