PostgreSQL INSERT ON CONFLICT 语句用法与实例

PostgreSQL INSERT ON CONFLICT 语句允许您在插入数据时处理一些数据冲突的情况，如果不存在冲突，则正常插入，如果存在冲突，可以更新已有的行。也就是说 INSERT ON CONFLICT 语句实现了 upsert 功能。

该 INSERT ON CONFLICT 语句是在 PostgreSQL 9.5 引入的。

PostgreSQL INSERT ON CONFLICT 语法

要在 PostgreSQL 实现 upsert 功能，请按照如下语法使用 INSERT ON CONFLICT 语句：

INSERT INTO table_name(column_list)
VALUES(value_list)
ON CONFLICT conflict_target conflict_action
[RETURNING {* | column_names}];;

相比较于 INSERT 语句来说，INSERT ON CONFLICT 只是多了 ON CONFLICT 子句。

这里：

• conflict_target 是存在冲突的对象，它可以是以下之一：

  • 一个列名。该列必须是主键或者唯一索引。
  • ON CONSTRAINT constraint_name， 并且 constraint_name 必须是唯一约束 的名称。
  • WHERE 子句。

• conflict_action 是存在冲突时要采取的动作，它可以是以下之一：

  • DO NOTHING: 如果存在冲突，不采取任何动作。
  • DO UPDATE: 如果存在冲突，使用 DO UPDATE SET column_1 = value_1, .. WHERE condition 更新表中的字段。

PostgreSQL INSERT ON CONFLICT 实例

我们要在 testdb 数据库中演示下面的示例。请先使用下面的语句创建 testdb 数据库：

CREATE DATABASE testdb;

选择 testdb 数据库为当前数据库：

\c testdb;

为了演示，我们需要创建一个新表，命名为 users。：

DROP TABLE IF EXISTS users;
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  nickname VARCHAR(50) NOT NULL,
  login_name VARCHAR(50) UNIQUE,
  notes VARCHAR(255)
);

这里，我们创建了一个 users 表，它有 id, nickname, login_name, 和 notes 四列， 其中 login_name 是一个唯一索引列。

让我们再使用 INSERT 语句插入一些行到 users 表：

INSERT INTO
    users (nickname, login_name, notes)
VALUES
    ('Tim', 'tim', 'This is Tim'),
    ('Tom', 'tom', 'This is Tom');

让我们再插入一个新行，其中带有和已有行重复的 login_name:

INSERT INTO
    users (nickname, login_name, notes)
VALUES
    ('Tim2', 'tim', 'This is Tim2');

ERROR:  duplicate key value violates unique constraint "users_login_name_key"
DETAIL:  Key (login_name)=(tim) already exists.

让我们使用 INSERT ON CONFLICT 语句重试一次，以便在存在重复 login_name 时采取一些动作。我们可以采取两种动作：

• 使用 DO NOTHING 表示不做任何事情：

  INSERT INTO
      users (nickname, login_name, notes)
  VALUES
      ('Tim2', 'tim', 'This is Tim2')
  ON CONFLICT (login_name) DO NOTHING;
  

  INSERT 0 0

  这里，我们使用 DO NOTHING 选项。然后，PostgreSQL 没有返回错误。

• 使用 DO UPDATE 更新其他的字段：

  INSERT INTO
      users (nickname, login_name, notes)
  VALUES
      ('Tim2', 'tim', 'This is Tim2')
  ON CONFLICT (login_name)
      DO UPDATE SET nickname = 'Tim2', notes = 'This is Tim2'
  RETURNING *;
  

  id | nickname | login_name |    notes
  ----+----------+------------+--------------
    1 | Tim2     | tim        | This is Tim2
  (1 row)

  在 DO UPDATE 子句中，您还可以使用 EXCLUDED 对象引用引发冲突的数据，上面的语句可以使用 EXCLUDED 修改为如下语句：

  INSERT INTO
      users (nickname, login_name, notes)
  VALUES
      ('Tim2', 'tim', 'This is Tim2')
  ON CONFLICT (login_name)
      DO UPDATE SET nickname = EXCLUDED.nickname,
                    notes = EXCLUDED.notes
  RETURNING *;
  

  在冲突对象中，除了字段名称，您还可以使用约束名称。上面的语句可以使用约束名称 users_login_name_key 代替列名 login_name:

  INSERT INTO
      users (nickname, login_name, notes)
  VALUES
      ('Tim3', 'tim', 'This is Tim3')
  ON CONFLICT ON CONSTRAINT users_login_name_key
      DO UPDATE SET nickname = EXCLUDED.nickname,
                    notes = EXCLUDED.notes
  RETURNING *;
  

  id | nickname | login_name |    notes
  ----+----------+------------+--------------
    1 | Tim3     | tim        | This is Tim3
  (1 row)

结论

PostgreSQL INSERT ON CONFLICT 实现了 upsert 功能，以便让您在使用同一个语句既可以 INSERT 也可以 UPDATE。