PostgreSQL-删除一个小时以上的数据，然后将新数据插入同一表 [英] PostgreSQL - Deleting data that are older than an hour, and then inserting new data to the same table

问题描述

我编写了一个名为 insert_offset_data（text，double precision）的函数，如下所示：

I wrote a function called insert_offset_data(text, double precision) as the following:

BEGIN
INSERT INTO tempoffset(id, location, offset_factor, ts_insert) 
VALUES (uuid_generate_v4(), location_in, offset_in, (now() at time zone 'utc'));

RETURN 1;
END;

但是，由于每当用户从iOS应用程序插入数据时，此函数都会在API调用中使用，希望在插入新数据之前先删除表中早于1小时的数据，因为iOS应用程序未考虑早于1小时的数据。在同一个函数中插入新数据之前，如何编写代码删除旧数据？

However, since this function is used in an API call whenever users insert data from an iOS application, I would love to delete data that are older than 1 hour in the table before inserting new data to it since the iOS application doesn't take into account data that are older than an hour. How do I write code to delete old data before I insert a new data in the same function?

推荐答案

添加以下内容：

DELETE FROM tempoffset WHERE ts_insert < now()-'1 hour'::interval;

这篇关于PostgreSQL-删除一个小时以上的数据，然后将新数据插入同一表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！