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?
Thanks in advance,
Walter