PostgreSQL中筛选最后100条数据并更新NULL时间戳字段的方法

要同时完成返回最后100条记录和仅更新其中timestamp为NULL的条目这两个操作，你可以借助PostgreSQL的WITH子句（CTE）结合UPDATE ... RETURNING语句实现，以下是具体方案：

方案1：基于主键筛选最后100条 #

假设你的表有自增主键（如id），可以通过主键倒序快速定位最后100条记录：

WITH last_100 AS (
    SELECT id
    FROM your_table
    ORDER BY id DESC
    LIMIT 100
)
UPDATE your_table t
SET timestamp_col = NOW()
FROM last_100 l
WHERE t.id = l.id
  AND t.timestamp_col IS NULL
RETURNING t.*;

方案2：基于任意排序字段筛选最后100条 #

如果需要按其他字段（如已有timestamp或创建时间）排序来定义“最后100条”，可以用ROW_NUMBER()窗口函数：

WITH ranked_records AS (
    SELECT *,
           ROW_NUMBER() OVER (ORDER BY create_time DESC) AS rn
    FROM your_table
),
last_100 AS (
    SELECT *
    FROM ranked_records
    WHERE rn <= 100
)
UPDATE your_table t
SET timestamp_col = NOW()
FROM last_100 l
WHERE t.id = l.id
  AND t.timestamp_col IS NULL
RETURNING t.*;

关键说明 #

• 替换your_table为你的实际表名，timestamp_col为目标timestamp字段名，id/create_time为你用来排序的字段
• UPDATE仅会修改属于最后100条且timestamp_col为NULL的记录，已有值的条目保持不变
• RETURNING t.*会返回所有100条记录（包含已更新和未更新的）

内容的提问来源于stack exchange，提问作者Vannian