在 PostgreSQL 中，新增或更新语句 操作意味着 UPDATE 或 INSERT 操作。 新增或更新语句 操作允许我们插入一行，或者如果一行已经存在则跳过插入操作并更新该行。

假设您想将一个表中的批量数据插入到另一个已经有一些数据的表中。在这种情况下，表中可能已经存在一些行，或者一些包含您想要更新的旧数据的行。 您可以跳过插入操作，或者如果已存在具有相同主键的记录，则更新某些字段，或者根据某些条件更新该行的某些字段。

语法

PostgreSQL 使用 INSERT 语句的 ON CONFLICT 子句实现 新增或更新语句 操作。 ON CONFLICT 指定在插入操作期间发生冲突时要采取的更新操作。

INSERT INTO <table_name> (column_list)
    VALUES(value_list)
[ON CONFLICT <conflict_targe> <conflict_action>,]
[RETURNING * or <column_name>];

在上面的 INSERT 语句中，conflict_target 可以是

  • 作为主键或具有唯一约束或具有唯一索引的列名。它不能是其他非唯一列。
  • ON CONSTRAINTconstraint_name 其中constraint_name可以是唯一的约束名称
  • 带有返回 TRUE 或 FALSE 谓词条件的 WHERE 子句。
  • DO NOTHING:它跳过插入操作并且不执行任何操作。如果您使用 DO NOTHING 子句，则不需要 conflict_target。
  • DO UPDATE 语句:使用 DO UPDATE SET column1 = value1,... WHERE - 更新表的字段。 ON CONFLICT DO UPDATE 确保明确执行插入或更新操作。

    • 例子

    要了解 新增或更新语句 操作的工作原理，让我们创建以下 employee 表。

    CREATE TABLE employee (
    emp_id 	INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    gender CHAR(1),
    birthdate DATE,
    email VARCHAR(100) UNIQUE,
    salary INT
);

    现在让我们使用 insert 语句向 employee 表中插入一行。

    INSERT INTO employee
VALUES(1,'Annie','Smith','F', DATE '1988-01-09', '[email protected]',5000);

    这将在 employee 表中插入一行，如下所示。

    PostgreSQL 更新或新增

    现在，如果再次执行相同的 INSERT 语句，则会引发错误

    PostgreSQL 更新或新增

    上面的 employee 表将 emp_id 作为主键，并已存在 emp_id = 1 的数据。 如果您尝试使用 emp_id = 1 插入新记录，则会引发错误。

    冲突跳过

    如果将 ON CONFLICT DO NOTHING 语句与 INSERT 语句一起使用，则不会引发任何错误，也不会向员工表中插入任何行，因为我们要求在发生冲突时不执行任何操作。

    INSERT INTO employee
VALUES(1,'Annie','Smith','F', DATE '1988-01-09', '[email protected]',5000)
ON CONFLICT DO NOTHING;

    上述语句将在 pgAdmin 中显示以下结果:

    PostgreSQL 更新或新增

    在上面的结果中，INSERT 0 0表示插入了0行并更新了0行。

    您可以使用 ON CONFLICT(column_name) DO NOTHING 实现与上述相同的功能，如下所示。这里的column_name应该是主键列或者具有唯一约束以确保所有值都是唯一的。

    INSERT INTO employee
VALUES(1,'Annie','Smith','F', DATE '1988-01-09', '[email protected]',5000)
ON CONFLICT(emp_id) DO NOTHING;

    或者，您还可以使用 ON CONFLICT ON CONSTRAINT 子句指定主键或唯一约束名称，如下所示。

    INSERT INTO employee
VALUES(1,'Annie','Smith','F', DATE '1988-01-09', '[email protected]',5000)
ON CONFLICT ON CONSTRAINT employee_pkey DO NOTHING;

    在上面的示例中，主键约束名称 employee_pkey 与 DO NOTHING 一起使用，表示如果某行违反主键约束则跳过插入操作。

    冲突更新

    如果发生冲突，ON CONFLICT DO UPDATE 语句将执行指定的更新语句。 例如，如果存在具有相同 emp_id 的行，则以下代码将更新现有行中的 last_name 列。

    INSERT INTO employee
VALUES(1,'Annie','Rizzolo','F', DATE '1988-01-09', '[email protected]',5000)
ON CONFLICT(emp_id) DO UPDATE SET last_name = 'Rizzolo';

    使用EXCLUDED表对象可以实现同样的效果，而不是在更新语句中指定显式值，如下所示。

    INSERT INTO employee
VALUES(1,'Annie','Rizzolo','F', DATE '1988-01-09', '[email protected]',5000)
ON CONFLICT(emp_id) DO UPDATE SET last_name = EXCLUDED.last_name;

    PostgreSQL 新增或更新

    在上面的结果中，INSERT 0 1表示插入0行并更新1行。 PostgreSQL会尝试插入一条记录，但由于emp_id列已经包含1，因此会发生冲突，并且会执行DO UPDATE SET last_name = EXCLUDED.last_name语句。

    EXCLUDED 是一个表对象，它指向指定 INSERT 语句的引用值。 此处，EXCLUDED.last_name 返回"Rizzolo"。

    我们检查一下表中的数据，可以看到 last_name 已更改为"Rizzolo"。

    PostgreSQL 新增或更新

    冲突条件更新

    使用ON CONFLICT WHERE子句可以根据某些条件跳过或执行更新操作。

    例如，如果发生冲突且现有行中存在 salary > 0，则以下代码将执行更新语句 UPDATE SET salary = 0。 如果salary为0然后跳过那么它将跳过插入或更新操作。

    INSERT INTO employee
VALUES(1,'Annie','Rizzolo','F', DATE '1988-01-09', '[email protected]',5000)
ON CONFLICT(emp_id) where salary > 0 
DO UPDATE SET salary = 0;

    上面的代码会将薪资字段更新为 0。

    请注意，您不能将 EXCLUDED 表与 WHERE 子句一起使用。

    因此，您可以使用 INSERT 语句执行 新增或更新语句 操作并管理冲突。