On Fri, 2005-09-23 at 14:53, Dawid Kuroczko wrote: > On 9/23/05, Yonatan Ben-Nes < da@xxxxxxxxxxxx <mailto:da@xxxxxxxxxxxx> > > wrote: > > Hi all, > > Every few days I need to DELETE all of the content of few tables and > INSERT new data in them. > The amount of new data is about 5 million rows and each row get about 3 > queries (INSERT + UPDATE). > <snip> > Or you could even try (haven't tested it): > BEGIN; > CREATE new_table; > SELECT INTO new_table * FROM temp_table; > DROP TABLE table; > ALTER TABLE new_table RENAME TO table; > COMMIT; -- leaving you with fresh 5mln new tuples table > ...with a risk of loosing all the changes made to old table after BEGIN; > yeah, i was thinking create newtable; ~~ load data into newtable begin; drop oldtable; alter table newtable rename to oldtable commit; this seperates the data loading piece from the piece where you promote the data to live data, plus then the time you have to hold the transaction open is only for the drop and rename, which will be quite fast. the only potential issues would be making sure you dont have FK/View type issues, but it doesn't sound like it would apply here. Robert Treat -- Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
