请求优化PostgreSQL插入查询：实现当日首行创建逻辑

原代码的问题在于：当当日无匹配数据时，CTE rows 返回空集，导致后续的 INSERT 没有数据可插入，无法创建当日首行。

修正后的代码（保留CTE写法） #

WITH rows AS (
    -- 获取当日最新的management_number
    SELECT management_number
    FROM management
    WHERE created_at >= CURRENT_DATE 
      AND created_at < CURRENT_DATE + INTERVAL '1 day'
      AND type = 'NORMAL'
    ORDER BY management_number DESC LIMIT 1
    -- 当日无数据时，返回默认值0
    UNION ALL
    SELECT 0 
    WHERE NOT EXISTS (
        SELECT 1 
        FROM management 
        WHERE created_at >= CURRENT_DATE 
          AND created_at < CURRENT_DATE + INTERVAL '1 day'
          AND type = 'NORMAL'
    )
)
INSERT INTO management(type, management_number)
SELECT 'NORMAL', management_number + 1
FROM rows
RETURNING *;

更简洁的写法（无需CTE） #

INSERT INTO management(type, management_number)
SELECT 'NORMAL', COALESCE(
    -- 取当日最大的management_number，无数据时返回null
    (SELECT management_number 
     FROM management 
     WHERE created_at >= CURRENT_DATE 
       AND created_at < CURRENT_DATE + INTERVAL '1 day'
       AND type = 'NORMAL'
     ORDER BY management_number DESC LIMIT 1),
    0 -- 空值时用0代替
) + 1
RETURNING *;

关键说明 #

1. 日期条件优化：用 CURRENT_DATE 动态获取当日日期，同时通过 created_at < CURRENT_DATE + INTERVAL '1 day' 确保只匹配当日的所有数据（而非从某个固定日期到当前时间）。
2. 空值处理：通过 UNION ALL 或 COALESCE 处理当日无数据的场景，确保始终有一个基础值（0），加1后得到首行的 management_number（即1）。

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