UPSERT 语句是 INSERT ON CONFLICT语句的简称。当UPSERT的指定值不违反唯一性约束时,插入数据,如果违反了唯一性约束,则更新这行数据。

注意事项

所需权限

用户必须拥有操作表的 INSERTUPDATE privileges

概要

图片 图片 图片

参数

Parameter Description
common_table_expr 请看Common Table Expressions.
table_name 表的名字
AS table_alias_name 表的别名,使用别名之后,会隐藏真正的表名。
column_name 插入期间要填充的列的名字
select_stmt 一个selection query。该查询返回的值的 data type必须与 =左边的列匹配。而且,如果在INTO之后列出了列名,则值必须按照对应的列排列;否则,值需要按照表中列的声明顺序进行排列。
DEFAULT VALUES 为了给所有列填充 default values, 在select_stmt中使用DEFAULT VALUES。为了使用默认值填充特定列,在该值保留在a_expr之外,或者在合适的位置使用DEFAULT
RETURNING target_list 基于插入值的返回值,在target_list中可以是表中具体的列名,其中,* 表示所有列,也可以是一个使用scalar expressions的计算。

在一个事务中, 使用 RETURNING NOTHING 不返回任何值。

如何将UPSERT 转化成 INSERT ON CONFLICT

UPSERT 仅仅考虑 [primary key](/develop/constraints/primary-key/列的唯一性。例如,假定ab是主键列,一下的的 UPSERTINSERT ON CONFLICT 是一样的:

> UPSERT INTO t (a, b, c) VALUES (1, 2, 3);

> INSERT INTO t (a, b, c)
    VALUES (1, 2, 3)
    ON CONFLICT (a, b)
    DO UPDATE SET c = excluded.c;

INSERT ON CONFLICT 是更加灵活的,可以考虑非主键列的唯一性。更多相关信息,请看 Upsert that Fails (Conflict on Non-Primary Key)

例子

Upsert 一行(无冲突)

在这个例子中,id列是主键,由于UPSERT语句中的id值与当前表中的值不冲突,所以会直接插入表中。

> SELECT * FROM accounts;
+----+----------+
| id | balance  |
+----+----------+
|  1 |  10000.5 |
|  2 | 20000.75 |
+----+----------+
> UPSERT INTO accounts (id, balance) VALUES (3, 6325.20);

> SELECT * FROM accounts;
+----+----------+
| id | balance  |
+----+----------+
|  1 |  10000.5 |
|  2 | 20000.75 |
|  3 |   6325.2 |
+----+----------+

Upsert 多行数据

在这个例子中,UPSERT语句插入多行数据。

> SELECT * FROM accounts;
+----+----------+
| id | balance  |
+----+----------+
|  1 |  10000.5 |
|  2 | 20000.75 |
|  3 |   6325.2 |
+----+----------+
> UPSERT INTO accounts (id, balance) VALUES (4, 1970.4), (5, 2532.9), (6, 4473.0);

> SELECT * FROM accounts;
+----+----------+
| id | balance  |
+----+----------+
|  1 |  10000.5 |
|  2 | 20000.75 |
|  3 |   6325.2 |
|  4 |   1970.4 |
|  5 |   2532.9 |
|  6 |   4473.0 |
+----+----------+

Upsert 更行一行数据 (在主键有冲突)

在这个例子中,id是主键,而且UPSERT语句中的id值在表中存在,所以UPSERT会更新列balance的值。

> SELECT * FROM accounts;
+----+----------+
| id | balance  |
+----+----------+
|  1 |  10000.5 |
|  2 | 20000.75 |
|  3 |   6325.2 |
|  4 |   1970.4 |
|  5 |   2532.9 |
|  6 |   4473.0 |
+----+----------+
> UPSERT INTO accounts (id, balance) VALUES (3, 7500.83);

> SELECT * FROM accounts;
+----+----------+
| id | balance  |
+----+----------+
|  1 |  10000.5 |
|  2 | 20000.75 |
|  3 |  7500.83 |
|  4 |   1970.4 |
|  5 |   2532.9 |
|  6 |   4473.0 |
+----+----------+

Upsert 失败(在非主键列中存在冲突 )

UPSERT中的数据违反了非主键列的唯一性约束,UPSERT不会更新行中的数据。在这个例子中,a是主键列,而且b列有 Unique constraint。由于插入b列的值不是唯一的,所以UPSERT会失败。

> SELECT * FROM unique_test;
+---+---+
| a | b |
+---+---+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+---+---+
> UPSERT INTO unique_test VALUES (4, 1);
pq: duplicate key value (b)=(1) violates unique constraint "unique_test_b_key"

在这样一个例子中,你需要使用 INSERT ON CONFLICT语句指定b列是需要考虑的唯一性约束列。

> INSERT INTO unique_test VALUES (4, 1) ON CONFLICT (b) DO UPDATE SET a = excluded.a;

> SELECT * FROM unique_test;
+---+---+
| a | b |
+---+---+
| 2 | 2 |
| 3 | 3 |
| 4 | 1 |
+---+---+

See Also