Re: delete/recreate indexes

Thanks Jeff,

On Oct 20, 4:51 am, pg(dot)(dot)(dot)(at)j-davis(dot)com (Jeff Davis) wrote:
> Also, to take a step back, why do you try to keep the timestamps
> changing like that? Why not store the information you need in the record
> (e.g. insert time as well as the datum) and then compute the result you
> need using a SELECT (or make it a view for convenience)? Fundamentally,
> these records aren't changing, you are just trying to interpret them in
> the context of the current day. That should be done using a SELECT, not
> an UPDATE.

Well this is not the way my "production" table is getting updated.
This was a developer's test DB so I thought the update statement would
be a
quick way to just shift all the values.

To mimic how my "production" database is being updated I should be
doing
this once each morning:

1. delete the old entries older than 6 days (i.e.: my table holds
one week's data)
2. add new entries for yesterday

I'm doing this via a perl script. For 1. I just do a
DELETE FROM device WHERE datum < (CURRENT_DATE - interval ' 7 days' )

For 2. I tried this but I get an "invalid input syntax for type
timestamp:" error:
my $val1 = rand(100);
my $val2 = rand(100);
my $stmt = "INSERT INTO data (device,group,datum,val1,val2)
VALUES(?,?,?,?,?)";
my $insert = $dbh->prepare($stmt) or die $dbh->errstr;
my $timestamp = "TO_TIMESTAMP(text(CURRENT_DATE - interval '1
day'),'YYYY-MM-DD HH24:MI:SS')";
$insert->execute($device,$groupid,$timestamp,$val1,$val2));

Alan