Re: Field sizes and structure

On Mon, 9 Dec 2002, Robert Fitzpatrick wrote:

> This is my first message to the list, I hope it is not so simple that it
> offends anyone, but the 2 books I have do not seem to answer the
> questions I have and searching different archives seem to present
> thousands of posts to review.
>
> If possible, once a table is created, how can a field size be changed?
> And also, the field position in the table? Not that I've had a need for
> position and can't thing of why it would matter, just thought I'd add
> that one to see if it is possible in the future.

There are ways of messing about with system tables to change the size of a
field. These can have horrible, unpredictable consequences, but many
folks seem to be able to do such things and are happy with the results.

Backup all data before trying to do anything like that.

The other way to do it. Assuming you either have no indexes or foreigh
keys (yet) or don't mind recreating them at some later date, you can do it
something like this:

create table t1 (name varchar(20), address varchar(30), state char(2), zip
varchar(5));

insert data here...

Now to convert the old table, because you need to handle different
lengths:

begin;
select name::varchar(40), address::varchar(45), state char(4),
zip::varchar(10) into t2 from t1;

The new table will have no indexes and such, so now would be the time to
create them... then..

alter table t1 rename to t1_old;
alter table t2 rename to t1;
create index...
create constraint...
commit; (Assuming it all looks good. If it don't then issue a rollback
instead)

As for changing the order, it's basically the same. You can either use
the select into (or create table, insert into) or you can use pg_dump /
pg_restore to do it with some editing by hand or a script you can write.