How can I delete rows if some condition in postgres

Question

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.

sticky bit · Accepted Answer · 2019-04-08 20:04:13Z

1

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.

answered Apr 8, 2019 at 20:04

sticky bit

37.7k12 gold badges34 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How can I delete rows if some condition in postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related