Postgres 14.5：实现插入时display_date自动逐日递增

我想要创建一个每日随机事实表，用于存储每日的事实列表。现有表结构如下：

create table random_facts
(
    id           serial primary key,
    fact         text      not null,
    display_date date      not null unique default now()
);

希望每次插入数据时，display_date字段能基于最后插入的记录自动递增一日。例如执行以下插入语句：

Insert into random_facts (fact)values (unnest(ARRAY ['The shortest war in history lasted 38 minutes', 'Russell Crowe plays Maximus in the movie Gladiator', 'Australia is wider than the moon.']));

期望数据结果如下：

请问如何实现每次插入记录时，display_date基于最后一行数据自动递增一日？我使用的是Postgres 14.5版本。

针对PostgreSQL 14.5，提供两种可行方案：

方案一：插入时直接生成递增日期序列 #

无需修改表结构，在插入语句中通过CTE计算起始日期，为每条记录生成递增日期：

WITH max_date AS (
    -- 获取最新日期，表为空则用当前日期
    SELECT COALESCE(MAX(display_date), CURRENT_DATE) AS last_date
    FROM random_facts
)
INSERT INTO random_facts (fact, display_date)
SELECT 
    unnest(ARRAY [
        'The shortest war in history lasted 38 minutes', 
        'Russell Crowe plays Maximus in the movie Gladiator', 
        'Australia is wider than the moon.'
    ]),
    -- 为每个事实生成递增1天的日期
    last_date + (row_number() OVER ())::integer - 1
FROM max_date;

逻辑说明 #

• max_date CTE负责获取表中最新的display_date，如果表是空表，则使用当前日期CURRENT_DATE
• row_number() OVER ()为每个展开的数组元素生成从1开始的序号
• 通过last_date + (序号-1)实现日期依次递增1天，确保连续无重复

方案二：触发器自动处理（无需修改插入语句） #

如果希望保持插入语句简洁，可通过触发器自动计算display_date，步骤如下：

1. 创建触发器函数 #

CREATE OR REPLACE FUNCTION set_auto_increment_date()
RETURNS TRIGGER AS $$
DECLARE
    base_date date;
BEGIN
    -- 获取基准日期：表中最新日期或当前日期
    SELECT COALESCE(MAX(display_date), CURRENT_DATE) INTO base_date
    FROM random_facts;
    
    -- 计算当前插入记录的日期：基准日期 + 表中已有记录数
    NEW.display_date := base_date + (SELECT COUNT(*) FROM random_facts)::integer;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

2. 创建触发器 #

CREATE TRIGGER trigger_auto_set_display_date
BEFORE INSERT ON random_facts
FOR EACH ROW
EXECUTE FUNCTION set_auto_increment_date();

使用说明 #

创建完成后，直接执行你原本的插入语句即可：

Insert into random_facts (fact)values (unnest(ARRAY ['The shortest war in history lasted 38 minutes', 'Russell Crowe plays Maximus in the movie Gladiator', 'Australia is wider than the moon.']));

触发器会自动为每条插入的记录分配连续递增的display_date，且满足唯一约束要求。

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