PostgreSQL 9.4：引用表无对应值时向表插入行的实现

首先，你遇到的语法错误是因为coalesce()的参数只能是返回单个值的表达式，而INSERT ... RETURNING语句不能直接作为它的参数放在那个位置，PostgreSQL不支持这种嵌套写法。

好在PostgreSQL 9.4还是有办法实现你想要的逻辑——仅当员工不存在时才插入，然后复用其ID写入worklog，下面提供两种可行方案：

方案1：用PL/pgSQL函数封装逻辑（推荐） #

这种方式逻辑清晰，复用性强，而且因为员工不存在的场景极少，大部分时候只需要一次查询，性能表现很好。

首先创建一个获取员工ID的函数：

CREATE OR REPLACE FUNCTION get_employee_id(p_name character(32))
RETURNS integer AS $$
DECLARE
    emp_id integer;
BEGIN
    -- 先尝试查找已有员工
    SELECT id INTO emp_id FROM employee WHERE name = p_name;
    
    -- 如果没找到，插入新员工并获取其ID
    IF emp_id IS NULL THEN
        INSERT INTO employee (name) VALUES (p_name) RETURNING id INTO emp_id;
    END IF;
    
    RETURN emp_id;
END;
$$ LANGUAGE plpgsql VOLATILE;

然后用这个函数插入worklog：

INSERT INTO worklog (activity, employee)
VALUES ('work in progress', get_employee_id('jonathan'));

如果需要处理并发场景（比如多个会话同时插入同一个新员工），可以在查询时加上FOR UPDATE锁避免冲突，修改函数里的SELECT语句：

SELECT id INTO emp_id FROM employee WHERE name = p_name FOR UPDATE;

方案2：用可写CTE一次性执行（无需创建函数） #

PostgreSQL 9.4支持可写CTE（Common Table Expressions），可以通过CTE先尝试插入新员工（仅当不存在时），再获取ID插入worklog：

WITH attempted_insert AS (
    -- 仅当员工不存在时执行插入
    INSERT INTO employee (name)
    SELECT 'jonathan'
    WHERE NOT EXISTS (SELECT 1 FROM employee WHERE name = 'jonathan')
    RETURNING id
)
INSERT INTO worklog (activity, employee)
VALUES ('work in progress', 
    -- 优先取已存在的员工ID，没有则取CTE中插入返回的ID
    COALESCE(
        (SELECT id FROM employee WHERE name = 'jonathan'),
        (SELECT id FROM attempted_insert)
    )
);

注意事项： #

这个方案在高并发场景下可能会遇到唯一约束冲突错误——如果两个会话同时判断员工不存在，都会执行插入操作，第二个会话会触发employee_name_ukey的唯一约束报错。如果你的业务并发量不高，这种情况概率极低，可以忽略；如果需要避免，可以结合事务和锁来处理，或者优先选择方案1。

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