UPSERT

Function

UPSERT inserts rows into a table. When a row duplicates an existing primary key or unique key value, the row will be ignored or updated.

The UPSERT syntax is supported only in 8.1.1 and later.

Syntax

For details, see Syntax of INSERT. The following table describes the syntax of UPSERT.

INSERT INTO ON DUPLICATE KEY UPDATE

INSERT IGNORE
INSERT INTO ON CONFLICT DO NOTHING

INSERT INTO ON CONFLICT(...) DO UPDATE SET
INSERT INTO ON CONFLICT ON CONSTRAINT con_name DO UPDATE SET

INSERT INTO ON CONFLICT(...) DO NOTHING
INSERT INTO ON CONFLICT ON CONSTRAINT con_name DO NOTHING

Precautions

• Do not concurrently update the same column-store table using the UPDATE and UPSERT statements.
• For more information about development and design specifications, see Development and Design Proposal.

• When UPSERT is executed on column-store tables, enable DELTA tables to avoid small CUs from. A large number of small CUs may cause space occupation poor query performance.
• UPSERT, UPDATE, and DELETE operations cannot be concurrently performed because they need to wait for the CU lock. This problem cannot be solved even if the DELTA table is enabled. To execute UPSERT, UPDATE, and DELETE operations concurrently on column-store tables, use H-Store tables.
• Only users with the INSERT or UPDATE permission on a table can run the UPSERT statement to insert data to or update data in the table.
• The UPSERT statement of updating data upon conflict can be executed only when the target table contains a primary key or unique index.
• The UPSERT statement of updating data upon conflict cannot be executed if no unique indexes are available. You can execute the statement only after the indexes are rebuilt.
• A distributed deadlock may occur, resulting in query hanging.
  

  For example, multiple UPSERT statements are executed in batches in a transaction or through JDBC (setAutoCommit(false)). Multiple similar tasks are executed at the same time.

  Possible result: The update sequences of different threads may vary depending on nodes. As a result, a deadlock may occur when the same row is concurrently updated.

  Solution:

  1. Decrease the value of the GUC parameter lockwait_timeout. The default value is 20 minutes. A distributed deadlock error will be reported after waiting for the value of lockwait_timeout. You can decrease the value of this parameter to reduce the service waiting time caused by a deadlock.
  2. Ensure that data with the same primary key is imported from only one database connection to the database. UPSERT statements can be executed concurrently.
  3. Only one UPSERT statement is executed in each transaction. UPSERT statements can be executed concurrently.
  4. Multiple UPSERT statements are executed in a single thread. UPSERT statements cannot be executed concurrently.

  In the preceding solution, method 1 can only reduce the waiting time but cannot solve the deadlock problem. If there are UPSERT statements in the service, you are advised to decrease the value of this parameter. Methods 2, 3, and 4 can solve the deadlock problem, but method 2 is recommended because its performance is better than another two methods.

• The distribution column cannot be updated. (Exception: Update is allowed if the distribution key is the same as the updated value.)

  1 2 3

  CREATE TABLE t1(dist_key int PRIMARY KEY, a int, b int); INSERT INTO t1 VALUES(1,2,3) ON CONFLICT(dist_key) DO UPDATE SET dist_key = EXCLUDED.dist_key, a = EXCLUDED.a + 1; INSERT INTO t1 VALUES(1,2,3) ON CONFLICT(dist_key) DO UPDATE SET dist_key = dist_key, a = EXCLUDED.a + 1;

• The UPSERT statement cannot be executed on the target table that contains a trigger (with the INSERT or UPDATE trigger event).
• The UPSERT statement is not supported for updatable views.
• The UPDATE clause, the WHERE clause of UPDATE, and the index condition expression should not contain functions that cannot be pushed down.
• Unique indexes cannot be deferred.
• When performing the update operation of UPSERT using INSERT INTO SELECT, pay attention to the query result sequence of SELECT. In a distributed environment, if the ORDER BY statement is not used, the sequence of returned results may be different each time the same SELECT statement is executed. As a result, the execution result of the UPSERT statement does not meet the expectation.
• Multiple updates are not supported. If a conflict occurs when multiple groups of data are inserted, an error similar to INSERT ON CONFLICT DO UPDATE command cannot affect row a second time is reported (except when the query plan is a PGXC plan).

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44

  CREATE TABLE t1(id int PRIMARY KEY, a int, b int); SET explain_perf_mode=pretty; -- Use the stream query plan: EXPLAIN (COSTS OFF) INSERT INTO t1 VALUES(1,2,3),(1,5,6) ON CONFLICT(id) DO UPDATE SET a = EXCLUDED.a + 1; QUERY PLAN ------------------------------------------------ id | operation ----+----------------------------------------- 1 | -> Streaming (type: GATHER) 2 | -> Insert on t1 3 | -> Streaming(type: REDISTRIBUTE) 4 | -> Values Scan on "*VALUES*" Predicate Information (identified by plan id) --------------------------------------------- 2 --Insert on t1 Conflict Resolution: UPDATE Conflict Arbiter Indexes: t1_pkey ====== Query Summary ===== ------------------------------ System available mem: 819200KB Query Max mem: 819200KB Query estimated mem: 3104KB (18 rows) INSERT INTO t1 VALUES(1,2,3),(1,5,6) ON CONFLICT(id) DO UPDATE SET a = EXCLUDED.a + 1; ERROR: dn_xxxx: INSERT ON CONFLICT DO UPDATE command cannot affect row a second time HINT: Ensure that no rows proposed for insertion within the same command have duplicate constrained values. -- Disable the stream plan and generate a PGXC plan: set enable_stream_operator = off; EXPLAIN (COSTS OFF) INSERT INTO t1 VALUES(1,2,3),(1,5,6) ON CONFLICT(id) DO UPDATE SET a = EXCLUDED.a + 1; QUERY PLAN ----------------------------------------------- id | operation ----+---------------------------------- 1 | -> Insert on t1 2 | -> Values Scan on "*VALUES*" Predicate Information (identified by plan id) --------------------------------------------- 1 --Insert on t1 Conflict Resolution: UPDATE Conflict Arbiter Indexes: t1_pkey Node expr: id (11 rows) INSERT INTO t1 VALUES(1,2,3),(1,5,6) ON CONFLICT(id) DO UPDATE SET a = EXCLUDED.a + 1; INSERT 0 2

Examples

Create table reason_t2 and insert data into it.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

CREATE TABLE reason_t2
(
  a    int primary key,
  b    int,
  c    int
);

INSERT INTO reason_t2 VALUES (1, 2, 3);
SELECT * FROM reason_t2 ORDER BY 1;
 a | b | c
---+---+---
 1 | 2 | 3
 (1 rows)

Insert two data records into the table reason_t2. One data record conflicts and the other does not. Conflicting data is ignored, and non-conflicting data is inserted.

1
2
3
4
5
6
7

INSERT INTO reason_t2 VALUES (1, 4, 5),(2, 6, 7) ON CONFLICT(a) DO NOTHING;
SELECT * FROM reason_t2 ORDER BY 1;
 a | b | c
---+---+----
 1 | 2 | 3
 2 | 6 | 7
(2 rows)

Insert two data records into the table reason_t2. One data record conflicts and the other does not. Conflicting data is updated, and non-conflicting data is inserted.

1
2
3
4
5
6
7
8

INSERT INTO reason_t2 VALUES (1, 4, 5),(3, 8, 9) ON CONFLICT(a) DO UPDATE SET b = EXCLUDED.b, c = EXCLUDED.c;
SELECT * FROM reason_t2 ORDER BY 1;
 a | b | c
---+---+----
 1 | 4 | 5
 2 | 6 | 7
 3 | 8 | 9
 (3 rows)

Filter the updated rows.

1
2
3
4
5
6
7
8

INSERT INTO reason_t2 VALUES (2, 7, 8) ON CONFLICT (a) DO UPDATE SET b = excluded.b, c = excluded.c  WHERE reason_t2.c = 7;
SELECT * FROM reason_t2 ORDER BY 1;
 a | b | c
---+---+---
 1 | 4 | 5
 2 | 7 | 8
 3 | 8 | 9
(3 rows)

Insert data into the table reason_t. Update the conflicting data and adjust the mapping. That is, update column c to column b and column b to column c.

1
2
3
4
5
6
7
8

INSERT INTO reason_t2 VALUES (1, 2, 3) ON CONFLICT (a) DO UPDATE SET b = excluded.c, c = excluded.b;
SELECT * FROM reason_t2 ORDER BY 1;
 a | b | c
---+---+---
 1 | 3 | 2
 2 | 7 | 8
 3 | 8 | 9
(3 rows)