PostgreSQL SELECT查询EXISTS条件用法及插入前双表校验实现

• SQL语法结构不合法：原代码将两个独立的SELECT子句用AND直接拼接在EXISTS条件内，完全不符合SQL语法规范，执行时会直接抛出解析错误
• 校验逻辑漏项：实际要求同时匹配emp_id和create_date两个字段，原代码仅校验emp_id，完全遗漏了create_date的匹配判断
• 表名笔误：要求校验的表是bill_item_temp，原代码误写为bill_item_ref，导致校验范围和实际业务表不符
• 参数数量不匹配：原SQL中占位符总数和传入的参数元组长度不一致，会直接触发参数数量校验错误
• 事务遗漏：Python执行DML操作后没有显式提交事务，数据不会真正写入数据库

核心逻辑：同时校验bill_item、bill_item_temp两张表中，是否存在和当前待插入记录完全匹配的emp_id + create_date组合，只有当两张表都找不到匹配记录时，才执行插入操作。

对应SQL写法 #

INSERT INTO bill_item (id, product_name, price, create_date, emp_id)
SELECT 
    %s, %s, %s, %s, %s
WHERE 
    -- 校验bill_item表无匹配记录
    NOT EXISTS (
        SELECT 1
        FROM bill_item
        WHERE emp_id = %s
          AND create_date = %s
    )
    -- 校验bill_item_temp表无匹配记录，两个条件同时满足才允许插入
    AND NOT EXISTS (
        SELECT 1
        FROM bill_item_temp
        WHERE emp_id = %s
          AND create_date = %s
    );

对应Python执行代码 #

s = CONN.cursor()
# 严格按照SQL占位符的顺序传参，避免顺序错配
params = (
    id, product_name, price, create_date, emp_id,  # 对应插入字段的5个值
    emp_id, create_date,  # 对应bill_item表的匹配条件参数
    emp_id, create_date   # 对应bill_item_temp表的匹配条件参数
)
s.execute(query, params)
CONN.commit() # 执行插入后必须提交事务才能生效

• 给bill_item、bill_item_temp两张表的emp_id + create_date字段组合建立联合索引，可大幅降低NOT EXISTS查询的耗时，避免全表扫描
• 高并发场景下，仅靠前置查询校验无法完全避免重复插入，可在bill_item表上为emp_id + create_date建立唯一约束，配合ON CONFLICT DO NOTHING语法做兜底，可靠性更高
• 校验时注意create_date的类型精度匹配，避免因时间精度截断（比如存储的毫秒差、时区差）导致的匹配失效

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