Postgres 11中Update遇约束冲突时如何静默返回0且不执行操作？

Postgres 11确实没有像INSERT那样原生支持ON CONFLICT DO NOTHING的UPDATE语法，但可以通过PL/pgSQL的异常捕获机制实现需求：当Update因约束冲突失败时，不执行任何操作并返回0。

方案一：封装为PL/pgSQL函数（推荐，支持返回受影响行数） #

编写一个通用函数，在函数内部执行Update语句，捕获常见的约束冲突异常，异常时返回0，正常执行时返回实际受影响行数。

示例函数（通用版） #

CREATE OR REPLACE FUNCTION safe_update(
    p_table text,
    p_set_clause text,
    p_where_clause text
) RETURNS integer AS $$
DECLARE
    affected_rows integer;
BEGIN
    -- 动态执行Update语句
    EXECUTE format('UPDATE %I SET %s WHERE %s', p_table, p_set_clause, p_where_clause)
    INTO affected_rows;
    RETURN affected_rows;
EXCEPTION
    -- 捕获常见的约束冲突异常
    WHEN unique_violation OR check_violation OR foreign_key_violation THEN
        RETURN 0;
END;
$$ LANGUAGE plpgsql;

调用方式 #

比如更新your_table中id=123的行，设置column1为'new_value'：

SELECT safe_update('your_table', 'column1 = ''new_value''', 'id = 123');

安全优化版（避免SQL注入） #

如果需要处理用户输入的参数，建议使用参数化动态SQL，避免注入风险：

CREATE OR REPLACE FUNCTION safe_update(
    p_table text,
    p_column text,
    p_new_value text,
    p_id integer
) RETURNS integer AS $$
DECLARE
    affected_rows integer;
BEGIN
    EXECUTE format('UPDATE %I SET %I = $1 WHERE id = $2', p_table, p_column)
    INTO affected_rows
    USING p_new_value, p_id;
    RETURN affected_rows;
EXCEPTION
    WHEN unique_violation OR check_violation OR foreign_key_violation THEN
        RETURN 0;
END;
$$ LANGUAGE plpgsql;

调用示例：

SELECT safe_update('your_table', 'column1', 'new_value', 123);

方案二：使用DO块（无返回值，仅静默执行） #

如果不需要返回受影响行数，只是希望Update冲突时不报错、不执行操作，可以用DO块直接包裹Update语句，捕获异常后什么都不做：

DO $$
BEGIN
    UPDATE your_table SET column1 = 'new_value' WHERE id = 123;
EXCEPTION
    WHEN unique_violation OR check_violation OR foreign_key_violation THEN
        -- 冲突时静默跳过
        NULL;
END $$;

说明 #

• 捕获的异常类型涵盖了最常见的约束冲突：unique_violation（唯一约束）、check_violation（检查约束）、foreign_key_violation（外键约束），如果有其他自定义约束类型，可以补充到EXCEPTION分支中。
• 函数方案的优势在于可以直接获取受影响行数，符合你“返回0”的需求；DO块则更适合不需要返回值的场景。

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