Hi Tino, --- Tino Wildenhain <[EMAIL PROTECTED]> wrote: > David Link wrote: > > Hi All, > > > > Here's a Conditional drop_table func for those interested. There > was a > > thread on this a long time back. > > > > We do this all the time : > > > > DELETE TABLE sales; > > CREATE TABLE sales (...); > > > Hm. "all the time" enables all the warning lights - > what are you doing to have to delete and create > the tables every time?
By 'all the time' i mean once a week. (incidently, it is DROP and not DELETE table, of course). We are working with weekly loads of data. because of the volumns i'm using a separate sales table per week, (ie, sale_200301, sale_200302, etc), becuase when it was in one big happy table (ie, sale) it is slower -- especially recreating indexes. Now you can see, to make the weekly data load process rerunnable (and we do rerun it often) i drop and [re]create this weeks collection of sales related tables. The logs contain statistics, warnings and errors, which we share with the non-geeks who use the data. Also, standard procedure (I believe) for maintaining a data model is creating and using database creation scripts (with DROP and CREATE) - so every time you set up a bran new system, you can get those (i believe) unnecessary messages: ERROR table does not exist. -Thanks. > > > But nobody likes > > > > ERROR: table "sales" does not exist > > > > which we see all the time in the logs. I want to show the logs to > none > > db folk -- so we can't have those error messages in it. > > grep -v "ERROR" should do it. Yes, but then you've got to wrap things in a log filter to generate reports rather than just using the processing log as it comes out. > > > > > (There must be some explaination why postgresql (and Oracle as > well) do > > not have CREATE OR REPLACE TABLE as it does for VIEWs, and > FUNCTIONs. > > Anybody know?) > > Nobody needs this? > There is: > > 1) delete from table; > 2) truncate table; > > to remove all the data > > 3) alter table ... > > to change tables layout. these do not create the table should it not yet exist. and i needed it. -- so i wrote the simple stored procedure to make meself happy. i noticed others have asked for a solution to the problem as well. (are you being a stickler?) also in the name of consistency, CREATE OR REPLACE exist for stored procedures (and views?). PS: I LOVE Postegres. It has made my life (as an Oracle DBA) charming rather than hell! Thanks. > > HTH > Tino Wildenhain > __________________________________ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
