Re: delete/recreate indexes

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Thanks Jeff,

On Oct 20, 4:51 am, pg...@xxxxxxxxxxx (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

-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance



[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux