Re: Table definition changes when a row is dropped and

On Tue, 2006-12-05 at 00:42, deep ... wrote:
> Hi everyone,
>
> I have a java app using pgsql 7.3 on unix platform where the latest
> version of the app updates the width of a row in a table as a part of
> upgrade. Since there is no straightforward 'modify' clause in
> Postgresql 7.3, we're using
>
> alter table ... add new_column type (width);
> update ... set new_column=old_column;
> alter table... drop old_column;
>
> After running this sql as a part of upgrade script, the table
> definition changes in database, in that the widened column gets pushed
> to the end of the definition (as can be seen through \d table_name
> command). This is making the insert queries fail which were designed
> with the original table definition in mind.
>
> Assuming there is no way to know whether the insert command is running
> before or after upgrade (from within the database handler part of the
> app, I mean), how do we solve this problem? Backup data, Drop the
> table and create it again? In that case, what happens to the indexes
> and constraints?

The real problem is that your insert statements are broken. Whether you
decide to fix that problem or not is the first question. Assuming that
you're NOT going to fix that problem, and you're willing to live with
the problems it's gonna cause next time and the time after that when you
do something like alter your table, then you have a few options.

One is to backup everything, alter the schema defs, and then restore.
With an older version of postgresql, like 7.3, I'm pretty sure you'll
have to dump as insert statements to get it to work right, because back
in the 7.3 days the columns weren't included in the copy commands in a
backup (I don't think they were, it's been a while).

Other option is to rename the table, and create a new one with the
structure you want and then do a:

insert into newtable (col1,col2,col3) (select col1,col2,col3 from
oldtable);

Me personally, I'd fix the insert statements in the application. But
I'm odd that way.