Clear History - Postgre SQL

Hi, i'm running traccar using Postgre SQL database, it looks like the updated "database history days" in the CONFIG file does not work with Postge SQL. I have attempted to implement the instructions on the Clear History page (https://www.traccar.org/clear-history/) but it appears that the SQL statement needs to be slightly different. Attempted various different statements , but nothing seems to be accepted. Is there anyone that has some guidance here ?

It seems to have a problem with the limit statement, but even if I remove that, it generates an error that the Operator does not exist.

DELETE FROM tc_positions WHERE servertime < (NOW(), - INTERVAL '89 days') AND tc_positions.id NOT IN (SELECT positionId FROM tc_devices WHERE positionId IS NOT NULL);

(Tried with servertime/fixtime as well as devicetime.)

It's written for MySQL. Postgres might obviously have a different syntax for some things. Have you read any relevant postgres documentation? Please provide links.

Hi Anton,

Thanks for the quick response, I have reviewed various postgres & mysql manuals to try and come up with different variations of the SQL statement, I had one working which is :

delete from tc_positions_1 where deviceid in (select id from tc_devices where groupid = 10) and servertime < (NOW() - INTERVAL '1 day');

This is obviously for the deletion of records belonging to a specific groupID (within my environment)

One of the sample tutorials that I reviewed is the below:

https://www.tutorialspoint.com/postgresql/postgresql_limit_clause.htm

I am specifically intrested in the limit clause, as I have about 100.000 records coming in daily.... I need to keep at least 89 days history
Once the SQL statement works, it should not be a problem to re-write the bash script to set a daily cron job....

It seems like Postgre SQL doesn't support limit for delete statements, so you basically need to do a select with limit and delete those selected rows.

ok, for reference to future users, the below syntax will work for Postgres.

DELETE FROM tc_positions WHERE tc_positions.id NOT IN (SELECT positionId FROM tc_devices WHERE positionId IS NOT NULL) AND servertime < (NOW() - INTERVAL '89 days');

Just does not have the limit, when I have this worked out I will respond again.

For those looking for a similar statement for postgre SQL:

DELETE FROM tc_positions WHERE id IN (SELECT id FROM tc_positions_1 LIMIT 10000) and tc_positions.id NOT IN (SELECT positionId FROM tc_devices WHERE positionId IS NOT NULL) AND fixtime < (NOW() - INTERVAL '90 days');

This works