Thanks for the suggestion. Dump/Reload the DB I am really not
going to be able to do. Also I am going to be doing this to several
copies of similar databases so vi is out, but that is nothing that a
nice script can't overcome. I think that the down time would just be
to much. Also because I am going to be running it on multiple
datbases I was hoping to limit it to a single script ran inside of
psql, as I already have a system in place to run given sql scripts on
each databases.
Other wise it is a very good suggestion that would same my much
trouble. I may still come back to it.
Thanks
Robert Perry
On Aug 1, 2005, at 12:48 PM, Mark R. Dingee wrote:
Robert,
If you have the luxury of taking the production db offline for a
few minutes
or if you're just making changes in a test environment, this
process works:
given:
create table test1 (
id integer,
txt1 text,
txt2 text
);
execute at command line:
pg_dump [dbname]> backups/renametable.sql
use your favorite text editor to change the above create table
statement in
backups/renametable.sql to:
create table test1 (
id integer,
txt0 text,
txt1 text,
txt2 text
);
execute at command line:
dropdb [dbname]; createdb [dbname]; cat backups/renametable.sql | psql
it's not the most graceful of methods, but it works.
Mark
On Monday 01 August 2005 12:06 pm, Robert Perry wrote:
Perhaps I am just just a bit anal on this but some columns I
really like to have as the last columns of a table. (usually
last_modby, last_modtime, type fields) Thus when I need to add a
column to a table I am not happy just adding the column. Instead I
go a little insane and rename the table, create the new table, copy
the data into the new table (with any require manipulations), change
all foreign key constraints to point to the new table, rebuild
plpgsql functions the user that table and finally drop the old table.
The problem is the second to last. (rebuild plpgsql function)
These function are getting a little harder to find. This DB has
hundreds if not thousands of functions and some of my table names are
a bit common. (e.g. item) My first of all I would really like a way
to tell postgresql to un cache all functions and let the cache
rebuild as need be. I imagine restarting postgresql would do this,
but in my particular situation that has its' own problems associated
with it.
Secondly does anyone know a way to insert a column instead of
appending a column to a table. That way I could just avoid the
entire mess.
Thanks
Robert Perry
---------------------------(end of
broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
---------------------------(end of
broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
