pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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



view thread (3+ messages)

Message-ID: <200409010932.24702.josh@agliodbs.com>
Permalink:  ../200409010932.24702.josh@agliodbs.com/
Also on:    postgresql.org/message-id/200409010932.24702.josh@agliodbs.com

 · 

reply

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