> ## Documentation Index
> Fetch the complete documentation index at: https://docs.cockroachlabs.com/llms.txt
> Use this file to discover all available pages before exploring further.

# UPSERT

The `UPSERT` [statement](/docs/stable/sql-statements.md) inserts rows in cases where specified values do not violate uniqueness constraints and updates rows in cases where values do violate uniqueness constraints. `UPSERT` considers uniqueness only for [primary key](/docs/stable/primary-key.md) columns.

> [!TIP]
>
> To read more about upserts, see our [Upsert in SQL: What is an Upsert, and When Should You Use One?](https://www.cockroachlabs.com/blog/sql-upsert/) blog post.

## `UPSERT` vs. `INSERT ON CONFLICT`

Assuming that columns `a` and `b` are the primary key, the following `UPSERT` and [`INSERT ... ON CONFLICT`](/docs/stable/insert#on-conflict-clause) statements are equivalent:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
UPSERT INTO t (a, b, c) VALUES (1, 2, 3);
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
INSERT INTO t (a, b, c)
  VALUES (1, 2, 3)
  ON CONFLICT (a, b)
  DO UPDATE SET c = excluded.c;
```

However, `UPSERT` does not let you specify columns to infer a unique constraint as an arbiter. An arbiter is a [`UNIQUE`](/docs/stable/unique) constraint used to check for conflicts during execution of `INSERT ... ON CONFLICT`. `UPSERT` always uses the primary key as the arbiter. You must therefore use `INSERT ... ON CONFLICT ... DO UPDATE` if your statement considers uniqueness for columns other than primary key columns. For an example, see [Upsert that fails (conflict on non-primary key)](#upsert-that-fails-conflict-on-non-primary-key).

When inserting or updating columns on a table that does not have [secondary indexes](/docs/stable/indexes.md), Cockroach Labs recommends using an `UPSERT` statement instead of `INSERT ON CONFLICT DO UPDATE`. Whereas `INSERT ON CONFLICT` always performs a read, the `UPSERT` statement writes without reading, making it faster. This may be useful if you are using a simple SQL table of two columns to [simulate direct KV access](/docs/stable/sql-faqs.md#can-i-use-cockroachdb-as-a-key-value-store).

If the table has a secondary index, there is no performance difference between `UPSERT` and `INSERT ON CONFLICT`. However, `INSERT` without an `ON CONFLICT` clause may not scan the table for existing values. This can provide a performance improvement over `UPSERT`.

To learn more about how to perform and when to use an upsert in CockroachDB, PostgreSQL, and MySQL, see [Upsert in SQL: What is an Upsert, and When Should You Use One?](https://www.cockroachlabs.com/blog/sql-upsert/).

## Considerations

* An `UPSERT` statement affecting a proper subset of columns behaves differently depending on whether or not you specify the target columns in the statement.

  * If you specify target columns (e.g., `UPSERT INTO accounts (id, name) VALUES (2, 'b2');`), the values of columns that do not have new values in the `UPSERT` statement will not be updated.
  * If you do not specify the target columns (e.g., `UPSERT INTO accounts VALUES (2, 'b2');`), the value of columns that do not have new values in the `UPSERT` statement will be updated to their default values.

  For examples, see [Upsert a proper subset of columns](#upsert-a-proper-subset-of-columns).

* A single [multi-row `UPSERT`](#upsert-multiple-rows) statement is faster than multiple single-row `UPSERT` statements. Whenever possible, use multi-row `UPSERT` instead of multiple single-row `UPSERT` statements.

* If the input data contains duplicates, see [Import data containing duplicate rows using `DISTINCT ON`](#import-data-containing-duplicate-rows-using-distinct-on).

## Required privileges

The user must have the `INSERT`, `SELECT`, and `UPDATE` [privileges](/docs/stable/security-reference/authorization.md#managing-privileges) on the table.

## Synopsis

![upsert syntax diagram](https://mintcdn.com/cockroachlabs/M6E8QStE4G5ISd4O/images/sql-diagrams/common/upsert.svg?fit=max\&auto=format\&n=M6E8QStE4G5ISd4O\&q=85\&s=09c76f6c0c66b00bab21e536f3a0ee8f)

## Parameters

| Parameter | Description |
| - | - |
| `common_table_expr` | See [Common Table Expressions](/docs/stable/common-table-expressions.md). |
| `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](/docs/stable/selection-queries.md). Each value must match the [data type](/docs/stable/data-types.md) 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](/docs/stable/default-value.md), 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](/docs/stable/scalar-expressions.md). Within a [transaction](/docs/stable/transactions.md), use `RETURNING NOTHING` to return nothing in the response, not even the number of rows affected. |

## 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.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM accounts;
```

```
+----+----------+
| id | balance  |
+----+----------+
|  1 |  10000.5 |
|  2 | 20000.75 |
+----+----------+
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> UPSERT INTO accounts (id, balance) VALUES (3, 6325.20);
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM accounts;
```

```
+----+----------+
| id | balance  |
+----+----------+
|  1 |  10000.5 |
|  2 | 20000.75 |
|  3 |   6325.2 |
+----+----------+
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> UPSERT INTO accounts (id, balance) VALUES (4, 1970.4), (5, 2532.9), (6, 4473.0);
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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`.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM accounts;
```

```
+----+----------+
| id | balance  |
+----+----------+
|  1 |  10000.5 |
|  2 | 20000.75 |
|  3 |   6325.2 |
|  4 |   1970.4 |
|  5 |   2532.9 |
|  6 |   4473.0 |
+----+----------+
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> UPSERT INTO accounts (id, balance) VALUES (3, 7500.83);
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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 uniqueness 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](/docs/stable/unique). Because the inserted `b` value is not unique, the `UPSERT` fails.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM unique_test;
```

```
+---+---+
| a | b |
+---+---+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+---+---+
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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`](/docs/stable/insert) statement to specify the `b` column as the column with the `UNIQUE` constraint.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> INSERT INTO unique_test VALUES (4, 1) ON CONFLICT (b) DO UPDATE SET a = excluded.a;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM unique_test;
```

```
+---+---+
| a | b |
+---+---+
| 2 | 2 |
| 3 | 3 |
| 4 | 1 |
+---+---+
```

### Upsert a proper subset of columns

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE TABLE accounts (
    id INT PRIMARY KEY,
    name STRING,
    balance DECIMAL(10, 2) DEFAULT 0
);
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> INSERT INTO accounts (id, name, balance) VALUES
    (1, 'a1', 10000.5),
    (2, 'b1', 20000.75),
    (3, 'c1',  6325.2);
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM accounts;
```

```
+----+------+----------+
| id | name | balance  |
+----+------+----------+
|  1 |   a1 | 10000.50 |
|  2 |   b1 | 20000.75 |
|  3 |   c1 |  6325.20 |
+----+------+----------+
```

Upserting a proper subset of columns without specifying the column names will write the default values of the unspecified columns when there is a conflict on the primary key. The account with `id` of `1` has a balance of `0` (the column's default value) after the `UPSERT`:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> UPSERT INTO accounts VALUES (1, 'a2');

> SELECT * FROM accounts;
```

```
+----+------+----------+
| id | name | balance  |
+----+------+----------+
|  1 |   a2 |     0.00 |
|  2 |   b1 | 20000.75 |
|  3 |   c1 |  6325.20 |
+----+------+----------+
```

If the target column names are included in the `UPSERT`, then the subset of columns without values will not change when there is a conflict on the primary key. The `balance` of the account with `id` of `2` is unchanged after the `UPSERT`:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> UPSERT INTO accounts (id, name) VALUES (2, 'b2');

> SELECT * FROM accounts;
```

```
+----+------+----------+
| id | name | balance  |
+----+------+----------+
|  1 |   a2 |     0.00 |
|  2 |   b2 | 20000.75 |
|  3 |   c1 |  6325.20 |
+----+------+----------+
```

### Import data containing duplicate rows using `DISTINCT ON`

If the input data to insert or update contains duplicate rows, you must
use [`DISTINCT ON`](/docs/stable/select-clause#eliminate-duplicate-rows) to
ensure there is only one row for each value of the primary key.

For example:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> WITH
    -- the following data contains duplicates on the conflict column "id":
    inputrows AS (VALUES (8, 130), (8, 140))

  UPSERT INTO accounts (id, balance)
    (SELECT DISTINCT ON(id) id, balance FROM inputrows); -- de-duplicate the input rows
```

The `DISTINCT ON` clause does not guarantee which of the duplicates is
considered. To force the selection of a particular duplicate, use an
`ORDER BY` clause:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> WITH
    -- the following data contains duplicates on the conflict column "id":
    inputrows AS (VALUES (8, 130), (8, 140))

  UPSERT INTO accounts (id, balance)
    (SELECT DISTINCT ON(id) id, balance
	 FROM inputrows
     ORDER BY balance); -- pick the lowest balance as value to update in each account
```

> [!NOTE]
>
> Using `DISTINCT ON` incurs a performance cost to search and eliminate duplicates.
> For best performance, avoid using it when the input is known to not contain duplicates.

## Limit the size of rows

To help you avoid failures arising from misbehaving applications that bloat the size of rows, you can specify the behavior when a row or individual [column family](/docs/stable/column-families.md) larger than a specified size is written to the database. Use the [cluster settings](/docs/stable/cluster-settings.md) `sql.guardrails.max_row_size_log` to discover large rows and `sql.guardrails.max_row_size_err` to reject large rows.

When you write a row that exceeds `sql.guardrails.max_row_size_log`:

* `INSERT`, `UPSERT`, `UPDATE`, `CREATE TABLE AS`, `CREATE INDEX`, `ALTER TABLE`, `ALTER INDEX`, `IMPORT`, or `RESTORE` statements will log a `LargeRow` to the [`SQL_PERF`](/docs/stable/logging#sql_perf) channel.
* `SELECT`, `DELETE`, `TRUNCATE`, and `DROP` are not affected.

When you write a row that exceeds `sql.guardrails.max_row_size_err`:

* `INSERT`, `UPSERT`, and `UPDATE` statements will fail with a code `54000 (program_limit_exceeded)` error.

* `CREATE TABLE AS`, `CREATE INDEX`, `ALTER TABLE`, `ALTER INDEX`, `IMPORT`, and `RESTORE` statements will log a `LargeRowInternal` event to the [`SQL_INTERNAL_PERF`](/docs/stable/logging#sql_internal_perf) channel.

* `SELECT`, `DELETE`, `TRUNCATE`, and `DROP` are not affected.

You **cannot** update existing rows that violate the limit unless the update shrinks the size of the
row below the limit. You **can** select, delete, alter, back up, and restore such rows. We
recommend using the accompanying setting `sql.guardrails.max_row_size_log` in conjunction with
`SELECT pg_column_size()` queries to detect and fix any existing large rows before lowering
`sql.guardrails.max_row_size_err`.

## See also

* [Ordering rows in DML statements](/docs/stable/order-by.md#ordering-rows-in-dml-statements)
* [Selection Queries](/docs/stable/selection-queries.md)
* [`DELETE`](/docs/stable/delete)
* [`INSERT`](/docs/stable/insert)
* [`UPDATE`](/docs/stable/update)
* [`TRUNCATE`](/docs/stable/truncate)
* [`ALTER TABLE`](/docs/stable/alter-table)
* [`DROP TABLE`](/docs/stable/drop-table)
* [`DROP DATABASE`](/docs/stable/drop-database)
* [SQL Statements](/docs/stable/sql-statements.md)