Postgres中能否用单语句实现带FOR UPDATE锁的Upsert并返回结果？

首先明确：不能直接在INSERT […] ON CONFLICT DO NOTHING RETURNING后附加WITH FOR UPDATE NOWAIT，PostgreSQL语法不支持这种写法——RETURNING子句仅返回插入操作产生的行，而FOR UPDATE NOWAIT是SELECT语句的锁控制语法，无法直接结合在INSERT的RETURNING之后。

不过可以通过CTE（公共表表达式）结合INSERT和SELECT，用单条原子语句实现你需要的逻辑：要么插入新的Pending记录并返回，要么锁定已存在的Pending记录并返回，同时避免并发冲突。

示例实现 #

假设你的表结构和部分唯一索引如下（用于确保同一实体仅一条Pending记录）：

CREATE TABLE enhancement_requests (
    id SERIAL PRIMARY KEY,
    entity_id VARCHAR(50) NOT NULL,
    status VARCHAR(20) NOT NULL,
    -- 其他业务字段
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 部分唯一索引：同一实体仅允许一条status='Pending'的记录
CREATE UNIQUE INDEX idx_pending_entity_unique ON enhancement_requests (entity_id) WHERE status = 'Pending';

对应的单条语句：

WITH insert_attempt AS (
    INSERT INTO enhancement_requests (entity_id, status)
    VALUES ('目标实体ID', 'Pending')
    ON CONFLICT (entity_id) WHERE status = 'Pending' DO NOTHING
    RETURNING *
)
SELECT * FROM insert_attempt
UNION ALL
SELECT * FROM enhancement_requests
WHERE entity_id = '目标实体ID' AND status = 'Pending'
FOR UPDATE NOWAIT;

逻辑说明 #

1. CTE部分：先尝试插入新的Pending记录，利用ON CONFLICT DO NOTHING避免重复插入；如果插入成功，insert_attempt会返回这条新行。
2. UNION ALL部分：如果CTE的INSERT没有返回结果（说明已存在对应实体的Pending记录），则执行SELECT语句，通过FOR UPDATE NOWAIT锁定该行并返回。
3. 原子性与并发控制：整个语句是原子执行的，不存在先INSERT再SELECT的竞态窗口；NOWAIT确保如果目标行已被其他进程锁定，当前语句会立即抛出错误，而非等待，适合异步作业的并发场景。

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