在PostgreSQL中模拟文件系统的文件创建行为

表结构 #

CREATE TABLE files (
    id   SERIAL  PRIMARY KEY,
    path VARCHAR NOT NULL UNIQUE
);

需求目标 #

模拟文件系统的文件创建逻辑：

• 若目标路径不存在，直接创建该路径
• 若路径已存在，创建格式为{filename} (1)的路径
• 若带编号的路径也存在，找到最大可用编号n，创建格式为{filename} ({n})的路径

优化实现方案 #

原查询逻辑复杂且未高效解决编号计算问题，以下是更简洁、通用的事务实现：

BEGIN;

-- 定义基础文件名（可替换为实际需要创建的文件名）
WITH base AS (SELECT 'kek' AS base_name),
-- 提取所有匹配的路径，拆分出对应的编号
existing AS (
    SELECT 
        CASE 
            WHEN path = base.base_name THEN 0
            ELSE substring(path from '\((\d+)\)$')::integer 
        END AS num
    FROM files, base
    WHERE path ~ ('^' || regexp_replace(base.base_name, '([\.\\\+\*\?\[\^\]\$\(\)\{\}\=\!\<\>\|\:])', '\\\1', 'g') || '(\s*\(\d+\))?$')
),
-- 计算下一个要使用的编号
next_num AS (
    SELECT COALESCE(MAX(num) + 1, 1) AS num
    FROM existing
)
-- 插入新路径
INSERT INTO files (path)
SELECT 
    CASE 
        WHEN EXISTS (SELECT 1 FROM existing WHERE num = 0) THEN 
            base.base_name || ' (' || next_num.num || ')'
        ELSE base.base_name
    END
FROM base, next_num
ON CONFLICT DO NOTHING; -- 处理极端并发场景下的冲突

COMMIT;

方案说明 #

1. 正则适配：用regexp_replace转义基础文件名中的正则特殊字符，确保匹配逻辑能适配任意文件名，而非硬编码特定值
2. 编号提取：将基础文件名的编号视为0，带括号的路径提取出数字编号，统一纳入编号计算
3. 编号计算：通过MAX(num)+1直接得到下一个可用编号，若没有任何匹配路径则默认使用1
4. 并发安全：事务包裹+ON CONFLICT DO NOTHING，避免高并发场景下的重复插入问题

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