PostgreSQL中统计数组内晚于指定时间戳的条目数及删除旧条目

当然可以用PostgreSQL纯SQL实现这两个需求，具体解法如下：

1. 统计数组中晚于指定时间戳的条目数量 #

通过unnest()将数组拆分为行，筛选出符合条件的记录后计数：

SELECT
  id,
  (SELECT COUNT(*)
   FROM unnest(ts_array) AS elem
   WHERE elem > '2023-01-01 00:00:00'::timestamp) AS late_count
FROM test_table;

注：替换test_table为你的表名，ts_array为时间戳数组列名，'2023-01-01 00:00:00'::timestamp为你指定的查询时间戳。

2. 删除数组中早于指定时间戳的条目 #

利用array_agg()结合unnest()筛选保留符合条件的元素，重新构建数组，支持查询查看或直接更新表数据：

仅查看过滤后的数组 #

SELECT
  id,
  array_agg(elem ORDER BY elem) AS filtered_ts_array
FROM test_table, unnest(ts_array) AS elem
WHERE elem >= '2022-01-01 00:00:00'::timestamp
GROUP BY id;

直接更新表中数组数据 #

UPDATE test_table
SET ts_array = (
  SELECT array_agg(elem ORDER BY elem)
  FROM unnest(ts_array) AS elem
  WHERE elem >= '2022-01-01 00:00:00'::timestamp
)
WHERE ts_array IS NOT NULL; -- 跳过空数组的处理

注：替换'2022-01-01 00:00:00'::timestamp为目标时间戳，ORDER BY elem可选，用于维持数组元素的时间顺序。

内容的提问来源于stack exchange，提问作者Ken - Enough about Monica