The UPSERT statement is short-hand for INSERT ON CONFLICT. It inserts rows in cases where specified values do not violate uniqueness constraints, and it updates rows in cases where values do violate uniqueness constraints.

Considerations

  • UPSERT considers uniqueness only for Primary Key columns. INSERT ON CONFLICT is more flexible and can be used to consider uniqueness for other columns. For more details, see How UPSERT Transforms into INSERT ON CONFLICT below.

  • When inserting/updating all columns of a table, and the table has no secondary indexes, UPSERT will be faster than the equivalent INSERT ON CONFLICT statement, as it will write without first reading. This may be particularly useful if you are using a simple SQL table of two columns to simulate direct KV access.

  • A single multi-row UPSERT statement is faster than multiple single-row UPSERT statements. Whenever possible, use multi-row UPSERT instead of multiple single-row UPSERT statements.

Required privileges

The user must have the INSERT and UPDATE privileges on the table.

Synopsis

WITH common_table_expr , UPSERT INTO table_name AS table_alias_name ( column_name , ) select_stmt DEFAULT VALUES RETURNING target_list NOTHING

Parameters

Parameter Description
common_table_expr See Common Table Expressions.
table_name The name of the table.
AS table_alias_name An alias for the table name. When an alias is provided, it completely hides the actual table name.
column_name The name of a column to populate during the insert.
select_stmt A selection query. Each value must match the data type of its column. Also, if column names are listed after INTO, values must be in corresponding order; otherwise, they must follow the declared order of the columns in the table.
DEFAULT VALUES To fill all columns with their default values, use DEFAULT VALUES in place of select_stmt. To fill a specific column with its default value, leave the value out of the select_stmt or use DEFAULT at the appropriate position.
RETURNING target_list Return values based on rows inserted, where target_list can be specific column names from the table, * for all columns, or computations using scalar expressions.

Within a transaction, use RETURNING NOTHING to return nothing in the response, not even the number of rows affected.

How UPSERT transforms into INSERT ON CONFLICT

UPSERT considers uniqueness only for primary key columns. For example, assuming that columns a and b are the primary key, the following UPSERT and INSERT ON CONFLICT statements are equivalent:

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

INSERT ON CONFLICT is more flexible and can be used to consider uniqueness for columns not in the primary key. For more details, see the Upsert that Fails (Conflict on Non-Primary Key) example below.

Examples

Upsert a row (no conflict)

In this example, the id column is the primary key. Because the inserted id value does not conflict with the id value of any existing row, the UPSERT statement inserts a new row into the table.

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

Upsert multiple rows

In this example, the UPSERT statement inserts multiple rows into the table.

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

Upsert that updates a row (conflict on primary key)

In this example, the id column is the primary key. Because the inserted id value is not unique, the UPSERT statement updates the row with the new balance.

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

Upsert that fails (conflict on non-primary key)

UPSERT will not update rows when the uniquness conflict is on columns not in the primary key. In this example, the a column is the primary key, but the b column also has the UNIQUE constraint. Because the inserted b value is not unique, the UPSERT fails.

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

In such a case, you would need to use the INSERT ON CONFLICT statement to specify the b column as the column with the UNIQUE constraint.

copy
icon/buttons/copy
> INSERT INTO unique_test VALUES (4, 1) ON CONFLICT (b) DO UPDATE SET a = excluded.a;
copy
icon/buttons/copy
> SELECT * FROM unique_test;
+---+---+
| a | b |
+---+---+
| 2 | 2 |
| 3 | 3 |
| 4 | 1 |
+---+---+

See also



Yes No