时间戳

首页 > 解决方案 > 删除新 Feed 中不存在的旧行

sql - 删除新 Feed 中不存在的旧行

问题描述

我有一个每天更新的数据提要，我需要将它插入到表格中。我想更新具有相同 id 但具有不同数据的行，添加不存在的数据并删除不再存在的行。我在最后一项任务中遇到问题，我似乎无法从以前的运行中删除新提要中不再存在的行。

这就是我所拥有的，带有一些测试数据的“列表”表：

INSERT INTO public.listings(
id, price, pub_date, rooms, publisher, updated_date)
VALUES 

(1, 100, '2021-05-03', 3, 'mf', '2021-01-01'),
(2, 200, '2021-05-03', 4, 'pmf', '2021-01-01'),
(3, 300, '2021-05-04', 5, 'jorge', '2021-01-01'),
(4, 400, '2021-05-05', 3, 'yuyu', '2021-01-01'),
(5, 500, '2021-05-06', 3, 'papo', '2021-01-01');

一个新的插入子句（更新提要）：

INSERT INTO listings AS p (id, price, pub_date, rooms, publisher, updated_date)
VALUES
  (1, 125, '2021-04-30', 4, 'mf', '2021-10-30')
, (2, 200, '2021-05-03', 4, 'pmf', '2021-01-01')
, (6, 250, '2021-04-30', 3, 'mf', '2021-10-30')
ON CONFLICT (id)  -- simple clause
DO UPDATE
SET    pub_date = EXCLUDED.pub_date
     , price     = EXCLUDED.price
     , rooms     = EXCLUDED.rooms
     , updated_date = EXCLUDED.updated_date
WHERE (p.pub_date, p.price, p.rooms, p.updated_date) IS DISTINCT FROM
      (EXCLUDED.pub_date, EXCLUDED.price, EXCLUDED.rooms, EXCLUDED.updated_date);

这是触发器：

CREATE OR REPLACE FUNCTION trg_prop_backup()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   INSERT INTO old_listings 
   VALUES (OLD.*);
   
   DELETE FROM listings --this sentence generates an error on id duplicity
   WHERE listings.id = OLD.id;
   
   RETURN NEW;
END
$func$;

CREATE TRIGGER prop_listings_upd_bef
BEFORE UPDATE OR DELETE ON listings
FOR EACH ROW EXECUTE FUNCTION trg_prop_backup();

这已经适用于更新现有行并插入新行，但它不适用于更新提要中不再存在的行。有些事情我做错了（或没有做），但我似乎无法提出解决方案。

“old_listings”表包含新提要中不存在的行，因为数据已更新或删除。

运行两个插入查询的结果必须是（对于列表表）：

  (1, 125, '2021-04-30', 4, 'mf', '2021-10-30')
, (2, 200, '2021-05-03', 4, 'pmf', '2021-01-01')
, (6, 250, '2021-04-30', 3, 'mf', '2021-10-30')

和 old_listings 表：

(1, 100, '2021-05-03', 3, 'mf', '2021-01-01'),
(3, 300, '2021-05-04', 5, 'jorge', '2021-01-01'),
(4, 400, '2021-05-05', 3, 'yuyu', '2021-01-01'),
(5, 500, '2021-05-06', 3, 'papo', '2021-01-01');

标签： sqlpostgresqltriggers

解决方案

这是我的建议：

  • series_ts在表中添加一个额外的时间戳字段，并在插入或更新时将listings其值设置为；current_timestamp
  • 删除查询的where子句，insert on conflict update以便series_ts即使对于等效记录也会更新；
  • 使用插入查询删除同一事务中不等于的所有listings记录。series_tscurrent_timestamp

为了做到这一点，首先创建一个执行此函数的“在插入或更新每一行之前”触发器：

create function set_listings_series_ts() returns trigger language plpgsql as $$
begin
 new.series_ts := current_timestamp;
 return new;
end; $$;

完成后运行：

BEGIN TRANSACTION;

INSERT INTO listings AS p (id, price, pub_date, rooms, publisher, updated_date)
VALUES
  (1, 125, '2021-04-30', 4, 'mf', '2021-10-30')
, (2, 200, '2021-05-03', 4, 'pmf', '2021-01-01')
, (6, 250, '2021-04-30', 3, 'mf', '2021-10-30')
ON CONFLICT (id)  -- simple clause
DO UPDATE
SET    pub_date = EXCLUDED.pub_date
     , price     = EXCLUDED.price
     , rooms     = EXCLUDED.rooms
     , updated_date = EXCLUDED.updated_date; -- your query w/o WHERE

WITH t as 
(
  delete from listings 
  where series_ts < current_timestamp 
  returning *
)
insert into old_listings select * from t; -- delete old data, save in old_listings

COMMIT TRANSACTION;

如果您更改“插入...更新”查询，您也可以使用 oa 触发器执行此操作。

推荐阅读