PostgreSQL定时删除表数据

妖狐艹你老母 2022-10-11 13:49 275阅读 0赞

## 背景 ##

有时候我们需要定时删除一些表数据比如一些log表，只需要存储7天的数据，如果用程序，比如java来处理。就需要写一个定时器来处理，比较麻烦。比较方便简单的做法就是使用触发器

这里我有一个 log表 想定时删除7天前的数据。

## 前置处理 ##

建表

create table logistics_xml
    (
        id       bigserial not null
            constraint logistics_xml_pk
                primary key,
        company  text,
        order_id text,
        xml      text,
        ctime    timestamp(0) default now()
    );

插入数据

insert into logistics_xml(id, company, order_id, xml, ctime) VALUES (1, '232', '2323', '2424', now());
    insert into logistics_xml(company, order_id, xml, ctime) VALUES ('232', '2323', '2', '2021-06-01 03:05:53');
    insert into logistics_xml(company, order_id, xml, ctime) VALUES ('66', '2323', '2', now());

## 实现 ##

1.  编写函数

CREATE OR REPLACE FUNCTION auto_del_logistics_xml ()
    RETURNS TRIGGER AS $res$
    BEGIN
       delete from logistics_xml where logistics_xml.ctime < (now() - interval '7 day');
    	 return NULL;
    END;
    $res$ LANGUAGE plpgsql;

1.  创建触发器

CREATE TRIGGER auto_del_user AFTER INSERT ON logistics_xml
    FOR EACH ROW EXECUTE PROCEDURE auto_del_logistics_xml();

> 触发器的一些介绍 https://www.runoob.com/postgresql/postgresql-trigger.html

这里主要将函数与表logistics\_xml绑定，触发事件为 INSERT。当然还可以修改为 其他事件

> 这种做法虽然可行，但是不是很建议，因为每次插入都要执行  
> delete from logistics\_xml where logistics\_xml.ctime < (now() - interval ‘7 day’)  
> 严重影响性能，这里基于业务作取舍

这里创建完成后我们再测试插入一条数据

insert into logistics_xml(company, order_id, xml, ctime) VALUES ('66', '2323', '2', now());

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNjUxOTA0_size_16_color_FFFFFF_t_70]可以看到之前插入6月1号的数据被删除了。

[参考博客][Link 1]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNjUxOTA0_size_16_color_FFFFFF_t_70]: /images/20221005/d214ac177381469784518807279d1539.png
[Link 1]: https://blog.csdn.net/qq_24095055/article/details/90239022