CODEWITHSUNDEEP
databasepostgresql
lisibuvowo
lisibuvowo

Reputation: 15

How can I delete rows if some condition in postgres

I have some values in the database.

In a row, I have a timestamp column.

So I have hash identification for the group.

I can get the oldest time from group by (QUERY A):

SELECT last_hit as time
FROM cache 
WHERE hash = 'bba04f6985f560446c122d235ed2e51bf7c10864' 
ORDER BY time DESC 
LIMIT 1

How can I remove all items (WHERE hash = 'bba04f6985f560446c122d235ed2e51bf7c10864) if the result of query is older then 30 minut?

Thank you for any help.

Upvotes: 1

Views: 2864

Answers (1)

sticky bit
sticky bit

Reputation: 37472

Take the current timestamp an subtract the timestamp in the column from it. Then compare to a 30 minute interval.

DELETE FROM cache
       WHERE hash = 'bba04f6985f560446c122d235ed2e51bf7c10864'
             AND statement_timestamp() - last_hit > '00:30:00'::interval;

statement_timestamp() gets the current timestamp when the statement begins. Other options are now() to get the timestamp of the beginning of the transaction and clock_timestamp() which changes during the execution of the statement. Change it to your needs. More information can be found in the documentation.

Upvotes: 1

Related Questions