Re: A conditional DROP TABLE function - Mailing list pgsql-general

Hi Tino,

--- Tino Wildenhain <tino@wildenhain.de> 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