[KLUG Members] PostgreSQL field resize?

[Mike Slack]

Here's a pretty simple trick that works nicely, unless your table is
HUGE.

(1) Back up your database just in case (REALLY, DO THIS).

(2) Dump database table:

    $ pg_dump -t tablename databasename > databasename.dmp

(3) Edit the file databasename.dmp and change the "create table" statement so
that your field has the length you want.

(4) Drop the original table (this is why you want a backup):

    $ psql databasename
    # drop table tablename;

Note: This step will fail if your table has references to it.  In that case
you will need to modify step (2) to include more tables, and then drop all of 
them in this step (in the proper order).  Alternatively, if your entire database 
is not too big, you could just use the entire database dump (i.e. remove "-t tablename") 
in step (2) and then drop the entire database in this step ($ dropdb databasename).

(5) Recreate the table:

    $ psql -f databasename.dmp databasename

Note: If you dropped the entire database in the last step, you will need to
create it again first ($ createdb databasename).

Hope this helps.

Mike

Kevin Mitchell (kevin@godzilla.iserv.net) wrote:
> Hiya,
> 
> Does anyone know if you can rezise the length of a field in a PostgreSQL
> database?
> 
> I have something like this:
> 
> Field		Type		Length
> -----		-----		------
> username	varchar()	8
> 
> and I'd like to make 8 to be a longer value (ie: 32).
> 
> TIA,
> 
> Kevin
> 
> _______________________________________________
> Members mailing list
> Members@kalamazoolinux.org
> 

-- 
Mike Slack
mike@slacking.org
--
"If we knew what it was we were doing, it wouldn't
be called research, would it?" --Albert Einstein