Two remarks:

• As I don't know your application, take this with a grain of salt, but: If I hear a thing like: "creating a table for the day eg 20030219 and" an alarm bell starts to ring in my head and it tries to say: Rethink your design! This doesn't sound right.
  
• As far as a quick glance (off site) got me, you will have to check the dbi-error before dying, as IF NOT EXISTS or the like is not mentioned.
  Code similar to
  

  
  $sth->execute();      # $sth is the statement-handle
  if ($dbh->errstr && $dbh->errstr =~ /Relation\s[a-zA-Z_-']+\salready\s
  +exists/) { # $dbh is the db-handle
      die ...
  }
  [download]

  should get you started. but I strongly suggest to rethink your design! instead.

regards,
tomte

$sth->execute or do_error($DBI::errstr);

sub do_error {
   my $error=shift;
  return if $error =~ /Relation already exists/;  # or whatever the ac
+tual error is
  die "A serious problem occured. According to the rumours: $error.\n"
+;
}
[download]

Update: the above answers your immediate question, but I agree with tomte that your database design probably needs some work.

CU
Robartes-

Thanks guys for the ideas I will just have to get the regular expression correct.

I am dealing with Netflow data which records flows through a router. I am just grouping the flow data from each day into a seperate table until it is a week old and then I will aggregate the data some more.

Does this still sound like a bad design?

Does this still sound like a bad design?

To my ears, it does :-)
The date of the day makes for a perfect column, if you think about it.


regards,
tomte

---

I am just grouping the flow data from each day into a seperate table until it is a week old and then I will aggregate the data some more. ... Does this still sound like a bad design?

Yes. By keeping data in separate tables, you're making more work for yourself. Databases are good at aggregating information, and it's easiest if all of the data is in a single table.

If I was doing this, I'd keep add a (non-key) field to hold the date, and would keep all data in a single file. Then, I could do something like

  SELECT sum(somefield)
  FROM table
  WHERE datefield BETWEEN ? AND ?
[download]

to do aggregate calculations. Or

  SELECT datefield, sum(somefield)
  FROM table
  WHERE datafield BETWEEN ? AND ?
  GROUP BY datefield
[download]

if I needed the aggregates to apply to distinct dates.

Flushing old data is easy.

  DELETE from TABLE WHERE datafield < ?
[download]

These are all static queries. By keeping all data in a single table, I don't have to reconstruct new queries to plug in a dynamic table name. And I'm letting the database do the aggregate calculations, rather than pulling all of the data over the wire to the client and doing it there.

If your particular calculations are more complicated, and can't be done in SQL, you may still be able to do part of the aggregation on the server side, reducing the amount of data you have to pull across the wire into the client.

Turn it into a function like 'table_exists(TEXT) RETURNS BOOLEAN'. See the function at http://www.brasileiro.net:8080/postgres/cookbook/view-recipes.adp?section_id=185&format=long

Seeking Green geeks in Minnesota

SELECT relhasindex, relkind, relchecks, reltriggers, relhasrules
FROM pg_class WHERE relname='day_20030218';
[download]