求助：将SQL Server每日自增编号触发器迁移至PostgreSQL

原SQL Server触发器的逻辑与问题 #

原触发器目标是每日从1开始为新增数据行生成递增编号，核心逻辑为：

• 获取插入行的日期值
• 查询当日number字段的最大值，加1得到新编号
• 更新插入行的number字段

但原写法存在明显缺陷：使用变量存储日期和编号时，若批量插入多行数据，变量仅会保留最后一行的日期值，导致所有插入行的number被设置为同一个值，不符合业务预期。

PostgreSQL的正确实现 #

PostgreSQL中推荐使用BEFORE INSERT触发器（避免AFTER INSERT后再更新的额外性能开销），结合行级触发逻辑处理单条/批量插入场景：

1. 创建触发器函数 #

CREATE OR REPLACE FUNCTION generate_daily_sequence()
RETURNS TRIGGER AS $$
DECLARE
    next_num INT;
BEGIN
    -- 获取当日已存在的最大number，无数据则取0加1
    SELECT COALESCE(MAX(number), 0) + 1
    INTO next_num
    FROM your_table_name
    WHERE dateField >= DATE(NEW."Date") 
      AND dateField < DATE(NEW."Date") + INTERVAL '1 day';

    -- 直接为当前插入行赋值编号
    NEW.number := next_num;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

2. 创建触发器 #

CREATE TRIGGER trigger_daily_number
BEFORE INSERT ON your_table_name
FOR EACH ROW EXECUTE FUNCTION generate_daily_sequence();

关键语法差异说明 #

• 日期转换：用PostgreSQL的DATE()函数替代SQL Server的CAST(... AS DATE)
• 日期运算：用INTERVAL '1 day'替代SQL Server的DateAdd(day,1,@from)
• 空值处理：用标准SQL函数COALESCE()替代SQL Server专属的ISNULL()（PostgreSQL也支持ISNULL，但COALESCE兼容性更强）
• 行级触发：采用FOR EACH ROW模式，确保每一行插入时单独计算当日编号，完美支持批量插入
• 字段访问：通过NEW关键字直接操作插入行的字段，无需额外关联查询

高并发场景优化 #

若存在高并发插入需求，为避免编号重复，可在查询时添加行锁：

SELECT COALESCE(MAX(number), 0) + 1
INTO next_num
FROM your_table_name
WHERE dateField >= DATE(NEW."Date") 
  AND dateField < DATE(NEW."Date") + INTERVAL '1 day'
FOR UPDATE;

注意事项 #

• 替换代码中的your_table_name为实际表名
• 若字段名包含大写字母（如原SQL中的Date），需用双引号包裹（PostgreSQL区分大小写）

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