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。

PostgreSQL INSERT ON CONFLICT 语法

PostgreSQL INSERT ON CONFLICT 实例

PostgreSQL `INSERT ON CONFLICT` 语法

PostgreSQL `INSERT ON CONFLICT` 实例