PostgreSQL INSERT ON CONFLICT 语句用法与实例

本文介绍了如何在 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