From: Josh Berkus <josh@agliodbs.com>
To: Michael Ryan S. Puncia <mpuncia@census.gov.ph>
To: pgsql-performance@postgresql.org
Subject: Re: Changing the column length
Date: Wed, 1 Sep 2004 09:32:24 -0700
Message-ID: <200409010932.24702.josh@agliodbs.com> (raw)
In-Reply-To: <200409010641.i816fDkJ025384@mail.census.gov.ph>
References: <200409010641.i816fDkJ025384@mail.census.gov.ph>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: josh@agliodbs.com, mpuncia@census.gov.ph
Subject: Re: Changing the column length
In-Reply-To: <200409010932.24702.josh@agliodbs.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox