INSERT - CockroachDB

The INSERT inserts one or more rows into a table. In cases where inserted values conflict with uniqueness constraints, the ON CONFLICT clause can be used to update rather than insert rows.

Required privileges

The user must have the INSERT on the table. To use ON CONFLICT, the user must also have the SELECT privilege on the table. To use ON CONFLICT DO UPDATE, the user must additionally have the UPDATE privilege on the table.

Synopsis

Parameters

Parameter	Description
`common_table_expr`	See .
`table_name`	The table into which data is written.
`AS table_alias_name`	An alias for the table name. When you provide an alias, it completely hides the actual table name.
`column_name`	The name of a column to populate during the insert.
`select_stmt`	A . Each value must match the 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 , 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. See the Insert Default Values examples below.
`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 . See the Insert and Return Values example below.

`ON CONFLICT` clause

Normally, when inserted values conflict with a UNIQUE constraint on one or more columns, CockroachDB returns an error. To update the affected rows instead, use an ON CONFLICT clause containing the column(s) with the unique constraint and the DO UPDATE SET expression set to the column(s) to be updated (any SET expression supported by the statement is also supported here, including those with WHERE clauses). To prevent the affected rows from updating while allowing new rows to be inserted, set ON CONFLICT to DO NOTHING. See the Update values ON CONFLICT and Do not update values ON CONFLICT examples. If the values in the SET expression cause uniqueness conflicts, CockroachDB will return an error. ON CONSTRAINT allows you to explicitly specify an index or unique constraint for INSERT ON CONFLICT, rather than inferring one using a column list, which is the default behavior.

`INSERT ON CONFLICT` vs. `UPSERT`

As an alternative to INSERT ... ON CONFLICT ... DO UPDATE, you can use the statement. For example, the following statements are equivalent:

INSERT INTO t (a, b, ..., n)
VALUES ('1', '2', ..., 'n')
ON CONFLICT DO UPDATE SET
  a = '1',
  b = '2',
  ...
  n = 'n';

UPSERT INTO t (a, b, ..., n)
VALUES ('1', '2', ..., 'n');

However, UPSERT does not let you specify columns to infer a unique constraint as an arbiter. An arbiter is a 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 . When inserting or updating columns on a table that does not have , 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 . 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.

Performance best practices

When generating and retrieving unique IDs, use the RETURNING clause with INSERT. See Insert and Return Values for details. In traditional SQL databases, you would do this using INSERT with SELECT, which is less performant.

Bulk inserts

Existing table
- Perform a multi-row INSERT in one statement in an .
- Do not use large batches of 100,000 rows or more, which can lead to long-running transactions that result in . If a multi-row INSERT results in an error code , Cockroach Labs recommends that you break up the INSERT into smaller batches. Experimentally determine the optimal batch size for your application by monitoring the performance for different batch sizes (1, 10, 100, 1000) rows in an implicit transaction. In some cases, for example, when a table has no secondary indexes, single row INSERTs may perform best in terms of total system throughput.
- You can also use the statement to bulk-insert CSV data.

Examples

Setup

To follow along, run to start a temporary, in-memory cluster with the sample dataset preloaded:

$ cockroach demo

Insert a single row

> INSERT INTO users (id, city, name, address, credit_card) VALUES
    ('c28f5c28-f5c2-4000-8000-000000000026', 'new york', 'Petee', '101 5th Ave', '1234567890');

> SELECT * FROM users WHERE city='new york';

                   id                  |   city   |       name       |           address           | credit_card
+--------------------------------------+----------+------------------+-----------------------------+-------------+
  00000000-0000-4000-8000-000000000000 | new york | Robert Murphy    | 99176 Anderson Mills        | 8885705228
  051eb851-eb85-4ec0-8000-000000000001 | new york | James Hamilton   | 73488 Sydney Ports Suite 57 | 8340905892
  0a3d70a3-d70a-4d80-8000-000000000002 | new york | Judy White       | 18580 Rosario Ville Apt. 61 | 2597958636
  0f5c28f5-c28f-4c00-8000-000000000003 | new york | Devin Jordan     | 81127 Angela Ferry Apt. 8   | 5614075234
  147ae147-ae14-4b00-8000-000000000004 | new york | Catherine Nelson | 1149 Lee Alley              | 0792553487
  c28f5c28-f5c2-4000-8000-000000000026 | new york | Petee            | 101 5th Ave                 | 1234567890
(6 rows)

If you do not list column names, the statement will use the columns of the table in their declared order:

> SHOW COLUMNS FROM users;

  column_name | data_type | is_nullable | column_default | generation_expression |  indices  | is_hidden
+-------------+-----------+-------------+----------------+-----------------------+-----------+-----------+
  id          | UUID      |    false    | NULL           |                       | {primary} |   false
  city        | VARCHAR   |    false    | NULL           |                       | {primary} |   false
  name        | VARCHAR   |    true     | NULL           |                       | {primary} |   false
  address     | VARCHAR   |    true     | NULL           |                       | {primary} |   false
  credit_card | VARCHAR   |    true     | NULL           |                       | {primary} |   false
(5 rows)

> INSERT INTO users VALUES
    ('1eb851eb-851e-4800-8000-000000000006', 'chicago', 'Adam Driver', '201 E Randolph St', '2468013579');

> SELECT * FROM users WHERE city IN ('chicago', 'new york');

                   id                  |   city   |       name       |            address             | credit_card
+--------------------------------------+----------+------------------+--------------------------------+-------------+
  1eb851eb-851e-4800-8000-000000000006 | chicago  | Adam Driver      | 201 E Randolph St              | 2468013579
  80000000-0000-4000-8000-000000000019 | chicago  | Matthew Clay     | 49220 Lisa Junctions           | 9132291015
  851eb851-eb85-4000-8000-00000000001a | chicago  | Samantha Coffey  | 6423 Jessica Underpass Apt. 87 | 9437219051
  8a3d70a3-d70a-4000-8000-00000000001b | chicago  | Jessica Martinez | 96676 Jennifer Knolls Suite 91 | 1601930189
  8f5c28f5-c28f-4000-8000-00000000001c | chicago  | John Hines       | 45229 Howard Manors Suite 22   | 7541086746
  947ae147-ae14-4800-8000-00000000001d | chicago  | Kenneth Barnes   | 35627 Chelsey Tunnel Suite 94  | 2099932769
  00000000-0000-4000-8000-000000000000 | new york | Robert Murphy    | 99176 Anderson Mills           | 8885705228
  051eb851-eb85-4ec0-8000-000000000001 | new york | James Hamilton   | 73488 Sydney Ports Suite 57    | 8340905892
  0a3d70a3-d70a-4d80-8000-000000000002 | new york | Judy White       | 18580 Rosario Ville Apt. 61    | 2597958636
  0f5c28f5-c28f-4c00-8000-000000000003 | new york | Devin Jordan     | 81127 Angela Ferry Apt. 8      | 5614075234
  147ae147-ae14-4b00-8000-000000000004 | new york | Catherine Nelson | 1149 Lee Alley                 | 0792553487
  c28f5c28-f5c2-4000-8000-000000000026 | new york | Petee            | 101 5th Ave                    | 1234567890
(12 rows)

Insert multiple rows into an existing table

See bulk inserts for best practices.

> INSERT INTO users (id, city, name, address, credit_card) VALUES
    ('8a3d70a3-d70a-4000-8000-00000000001b', 'seattle', 'Eric', '400 Broad St', '0987654321'),
    ('9eb851eb-851e-4800-8000-00000000001f', 'new york', 'Harry Potter', '214 W 43rd St', '5678901234');

> SELECT * FROM users WHERE city IN ('seattle', 'new york');

                   id                  |   city   |       name       |            address            | credit_card
+--------------------------------------+----------+------------------+-------------------------------+-------------+
  00000000-0000-4000-8000-000000000000 | new york | Robert Murphy    | 99176 Anderson Mills          | 8885705228
  051eb851-eb85-4ec0-8000-000000000001 | new york | James Hamilton   | 73488 Sydney Ports Suite 57   | 8340905892
  0a3d70a3-d70a-4d80-8000-000000000002 | new york | Judy White       | 18580 Rosario Ville Apt. 61   | 2597958636
  0f5c28f5-c28f-4c00-8000-000000000003 | new york | Devin Jordan     | 81127 Angela Ferry Apt. 8     | 5614075234
  147ae147-ae14-4b00-8000-000000000004 | new york | Catherine Nelson | 1149 Lee Alley                | 0792553487
  9eb851eb-851e-4800-8000-00000000001f | new york | Harry Potter     | 214 W 43rd St                 | 5678901234
  c28f5c28-f5c2-4000-8000-000000000026 | new york | Petee            | 101 5th Ave                   | 1234567890
  428f5c28-f5c2-4000-8000-00000000000d | seattle  | Anita Atkinson   | 27684 Laura Villages Suite 80 | 9800065169
  47ae147a-e147-4000-8000-00000000000e | seattle  | Patricia Herrera | 80588 Perez Camp              | 6812041796
  4ccccccc-cccc-4c00-8000-00000000000f | seattle  | Holly Williams   | 95153 Harvey Street Suite 5   | 2165526885
  51eb851e-b851-4c00-8000-000000000010 | seattle  | Ryan Hickman     | 21187 Dennis Village          | 1635328127
  8a3d70a3-d70a-4000-8000-00000000001b | seattle  | Eric             | 400 Broad St                  | 0987654321
(12 rows)

Insert from a `SELECT` statement

Suppose that you want MovR to offer ride-sharing services, in addition to vehicle-sharing services. You can create a drivers table from a subset of the users table.

> CREATE TABLE drivers (
    id UUID DEFAULT gen_random_uuid(),
    city STRING,
    name STRING,
    dl STRING UNIQUE CHECK (LENGTH(dl) < 8),
    address STRING,
    CONSTRAINT "primary" PRIMARY KEY (city ASC, id ASC)
);

> INSERT INTO drivers (id, city, name, address)
    SELECT id, city, name, address FROM users
    WHERE name IN ('Anita Atkinson', 'Devin Jordan');

> SELECT * FROM drivers;

                   id                  |   city   |      name      |  dl  |            address
+--------------------------------------+----------+----------------+------+-------------------------------+
  0f5c28f5-c28f-4c00-8000-000000000003 | new york | Devin Jordan   | NULL | 81127 Angela Ferry Apt. 8
  428f5c28-f5c2-4000-8000-00000000000d | seattle  | Anita Atkinson | NULL | 27684 Laura Villages Suite 80
(2 rows)

Insert default values

To check the for columns in a table, use the statement:

> SHOW CREATE TABLE drivers;

  table_name |                     create_statement
+------------+----------------------------------------------------------+
  drivers    | CREATE TABLE drivers (
             |     id UUID NOT NULL DEFAULT gen_random_uuid(),
             |     city STRING NOT NULL,
             |     name STRING NULL,
             |     dl STRING NULL,
             |     address STRING NULL,
             |     CONSTRAINT "primary" PRIMARY KEY (city ASC, id ASC),
             |     UNIQUE INDEX drivers_dl_key (dl ASC),
             |     FAMILY "primary" (id, city, name, dl, address),
             |     CONSTRAINT check_dl CHECK (length(dl) < 8)
             | )
(1 row)

If the DEFAULT value constraint is not specified and an explicit value is not given, a value of NULL is assigned to the column.

> INSERT INTO drivers (city, name) VALUES ('seattle', 'Bobby');

> INSERT INTO drivers (city, name, id) VALUES ('chicago', 'Terry', DEFAULT);

> SELECT * FROM drivers WHERE name in ('Bobby', 'Terry');

                   id                  |  city   | name  |  dl  | address
+--------------------------------------+---------+-------+------+---------+
  c8d36f0e-9eb4-439f-b3d0-029af184d24b | chicago | Terry | NULL | NULL
  af2e8122-bf87-4736-bde9-a42ad0857351 | seattle | Bobby | NULL | NULL
(2 rows)

To create a new row with only default values, use INSERT INTO DEFAULT VALUES. Running this command on the drivers table results in an error because the city column in drivers cannot be NULL, and has no default value specified.

> INSERT INTO drivers DEFAULT VALUES;

pq: null value in column "city" violates not-null constraint

Insert and return values

In this example, the RETURNING clause returns the id values of the rows inserted, which are generated server-side by the gen_random_uuid() function. The language-specific versions assume that you have installed the relevant .

Shell
Python
Ruby
Go
Node.js

> INSERT INTO drivers (id, city)
  VALUES (DEFAULT, 'seattle'), (DEFAULT, 'chicago')
  RETURNING id;

                   id
+--------------------------------------+
  b7750a60-91f2-404e-9cd1-5a3c310c1c9d
  c85e637e-2b03-4a52-bc54-1e1f6d7fd89b
(2 rows)

# Import the driver.

# Connect to the "movr" database.
conn = psycopg2.connect(
    database='movr',
    user='root',
    host='localhost',
    port=26257
)

# Make each statement commit immediately.
conn.set_session(autocommit=True)

# Open a cursor to perform database operations.
cur = conn.cursor()

# Insert two rows into the "drivers" table
# and return the "id" values generated server-side.
cur.execute(
    "INSERT INTO drivers (id, city) "
    "VALUES (DEFAULT, 'seattle'), (DEFAULT, 'chicago') "
    "RETURNING id"
)

# Print out the returned values.
rows = cur.fetchall()
print('IDs:')
for row in rows:
    print([str(cell) for cell in row])

# Close the database connection.
cur.close()
conn.close()

The printed values would look like:

IDs:
['cdd379e3-2d0b-4622-8ba8-4f0a1edfbc8e']
['4224b360-b1b0-4e4d-aba2-a35c64cdf404']

# Import the driver.
require 'pg'

# Connect to the "movr" database.
conn = PG.connect(
    user: 'root',
    dbname: 'movr',
    host: 'localhost',
    port: 26257
)

# Insert two rows into the "drivers" table
# and return the "id" values generated server-side.
conn.exec(
    "INSERT INTO drivers (id, city) "\
    "VALUES (DEFAULT, 'seattle'), (DEFAULT, 'chicago') "\
    "RETURNING id"
) do |res|

# Print out the returned values.
puts "IDs:"
    res.each do |row|
        puts row
    end
end

# Close communication with the database.
conn.close()

The printed values would look like:

IDs:
{"id"=>"cdd379e3-2d0b-4622-8ba8-4f0a1edfbc8e"}
{"id"=>"4224b360-b1b0-4e4d-aba2-a35c64cdf404"}

package main

	"database/sql"
	"fmt"
	"log"

	_ "github.com/lib/pq"
)

func main() {
	//Connect to the "movr" database.
	db, err := sql.Open(
		"postgres",
		"postgresql://root@localhost:26257/movr?sslmode=disable",
	)
	if err != nil {
		log.Fatal("error connecting to the database: ", err)
	}

	// Insert two rows into the "drivers" table
	// and return the "id" values generated server-side.
	rows, err := db.Query(
		"INSERT INTO drivers (id, city) " +
			"VALUES (DEFAULT, 'seattle'), (DEFAULT, 'chicago') " +
			"RETURNING id",
	)
	if err != nil {
		log.Fatal(err)
	}

	// Print out the returned values.
	defer rows.Close()
	fmt.Println("IDs:")
	for rows.Next() {
		var id string
		if err := rows.Scan(&id); err != nil {
			log.Fatal(err)
		}
		fmt.Printf("%s\n", id)
	}
}

The printed values would look like:

IDs:
cdd379e3-2d0b-4622-8ba8-4f0a1edfbc8e
4224b360-b1b0-4e4d-aba2-a35c64cdf404

var async = require('async')
var pg = require('pg')

// Config to connect to the "movr" database.
var config = {
    user: 'root',
    host: 'localhost',
    database: 'movr',
    port: 26257
  }

// Create pool
var pool = new pg.Pool(config)

pool.connect(function (err, client, done) {

    // Close communication with the database and exit.
    var finish = function () {
        done()
        process.exit()
    }

    if (err) {
        console.error('could not connect to cockroachdb', err)
        finish()
    }
  async.waterfall([
    function (next) {
      // Insert two rows into the "drivers" table
      // and return the "id" values generated server-side.
      client.query(
        `INSERT INTO drivers (id, city)
         VALUES (DEFAULT, 'seattle'), (DEFAULT, 'chicago')
         RETURNING id`,
        next
      )
    }
  ],
  function (err, results) {
    if (err) {
      console.error('error inserting into and selecting from drivers', err)
      finish()
    }
    // Print out the returned values.
    console.log('IDs:')
    results.rows.forEach(function (row) {
      console.log(row)
    })

    finish()
  })
})

The printed values would look like:

IDs:
{ id: 'cdd379e3-2d0b-4622-8ba8-4f0a1edfbc8e' }
{ id: '4224b360-b1b0-4e4d-aba2-a35c64cdf404' }

Update values `ON CONFLICT`

When a uniqueness conflict on columns (city, user_id, code) is detected, CockroachDB stores the rows proposed for insertion in a temporary table called excluded. This example demonstrates how you use the columns in the temporary excluded table to apply updates on conflict.

> INSERT INTO user_promo_codes (city, user_id, code, "timestamp", usage_count)
    VALUES ('new york', '147ae147-ae14-4b00-8000-000000000004', 'promo_code', now(), 1)
    ON CONFLICT (city, user_id, code)
    DO UPDATE SET usage_count = excluded.usage_count;

> SELECT * FROM user_promo_codes WHERE code = 'promo_code';

    city   |               user_id                |    code    |            timestamp             | usage_count
+----------+--------------------------------------+------------+----------------------------------+-------------+
  new york | 147ae147-ae14-4b00-8000-000000000004 | promo_code | 2022-06-01 14:23:52.262849+00:00 |           1
(1 row)

You can also update the row using an existing value:

> INSERT INTO user_promo_codes (city, user_id, code, "timestamp", usage_count)
    VALUES ('new york', '147ae147-ae14-4b00-8000-000000000004', 'promo_code', now(), 1)
    ON CONFLICT (city, user_id, code)
    DO UPDATE SET ("timestamp", usage_count) = (now(), user_promo_codes.usage_count + excluded.usage_count);

> SELECT * FROM user_promo_codes WHERE code = 'promo_code';

    city   |               user_id                |    code    |            timestamp             | usage_count
+----------+--------------------------------------+------------+----------------------------------+-------------+
  new york | 147ae147-ae14-4b00-8000-000000000004 | promo_code | 2022-06-01 14:26:50.697382+00:00 |           2
(1 row)

You can also use a WHERE clause to apply the DO UPDATE SET expression conditionally:

> INSERT INTO user_promo_codes (city, user_id, code, "timestamp", usage_count)
    VALUES ('new york', '147ae147-ae14-4b00-8000-000000000004', 'promo_code', now(), 3)
    ON CONFLICT (city, user_id, code)
    DO UPDATE SET ("timestamp", usage_count) = (now(), user_promo_codes.usage_count + excluded.usage_count)
    WHERE excluded.usage_count = 1;

> SELECT * FROM user_promo_codes WHERE code = 'promo_code';

    city   |               user_id                |    code    |            timestamp             | usage_count
+----------+--------------------------------------+------------+----------------------------------+-------------+
  new york | 147ae147-ae14-4b00-8000-000000000004 | promo_code | 2022-06-01 14:26:50.697382+00:00 |           2
(1 row)

This example uses the ON CONSTRAINT clause to explicitly specify the index user_promo_codes_pkey on which there is a conflict:

> INSERT INTO user_promo_codes (city, user_id, code, "timestamp", usage_count)
    VALUES ('new york', '147ae147-ae14-4b00-8000-000000000004', 'promo_code', now(), 1)
    ON CONFLICT ON CONSTRAINT user_promo_codes_pkey
    DO UPDATE SET usage_count =  user_promo_codes.usage_count + excluded.usage_count;

> SELECT * FROM user_promo_codes WHERE code = 'promo_code';

    city   |               user_id                |    code    |            timestamp             | usage_count
+----------+--------------------------------------+------------+----------------------------------+-------------+
  new york | 147ae147-ae14-4b00-8000-000000000004 | promo_code | 2022-06-01 14:26:50.697382+00:00 |           3
(1 row)

Do not update values `ON CONFLICT`

In this example, we get an error from a uniqueness conflict.

> INSERT INTO user_promo_codes (city, user_id, code, "timestamp", usage_count)
    VALUES ('new york', '147ae147-ae14-4b00-8000-000000000004', 'promo_code', now(), 1);

pq: duplicate key value violates unique constraint "user_promo_codes_pkey"

This example uses ON CONFLICT DO NOTHING to ignore the uniqueness error and prevent the affected row from being updated:

> INSERT INTO user_promo_codes (city, user_id, code, "timestamp", usage_count)
    VALUES ('new york', '147ae147-ae14-4b00-8000-000000000004', 'promo_code', now(), 1)
    ON CONFLICT (city, user_id, code)
    DO NOTHING;

> SELECT * FROM user_promo_codes WHERE code = 'promo_code';

    city   |               user_id                |    code    |            timestamp             | usage_count
+----------+--------------------------------------+------------+----------------------------------+-------------+
  new york | 147ae147-ae14-4b00-8000-000000000004 | promo_code | 2022-06-01 14:26:50.697382+00:00 |           2
(1 row)

In this example, ON CONFLICT DO NOTHING prevents the first row from updating while allowing the second row to be inserted:

> INSERT INTO user_promo_codes (city, user_id, code, "timestamp", usage_count)
    VALUES ('new york', '147ae147-ae14-4b00-8000-000000000004', 'promo_code', now(), 1), ('new york', '147ae147-ae14-4b00-8000-000000000004', 'new_promo', now(), 1)
    ON CONFLICT (city, user_id, code)
    DO NOTHING;

> SELECT * FROM user_promo_codes WHERE code in ('promo_code', 'new_promo');

    city   |               user_id                |    code    |            timestamp             | usage_count
+----------+--------------------------------------+------------+----------------------------------+-------------+
  new york | 147ae147-ae14-4b00-8000-000000000004 | new_promo  | 2022-06-01 14:30:16.666848+00:00 |           1
  new york | 147ae147-ae14-4b00-8000-000000000004 | promo_code | 2022-06-01 14:26:50.697382+00:00 |           2
(2 rows)

Import data containing duplicate rows using `ON CONFLICT` and `DISTINCT ON`

If the input data for INSERT ON CONFLICT contains duplicate rows, you must use to remove these duplicates. For example:

> WITH inputrows (city, user_id, code, "timestamp", usage_count)
    AS (VALUES ('new york', '147ae147-ae14-4b00-8000-000000000004'::uuid, 'promo_code', now()::timestamp, 0), ('new york', '147ae147-ae14-4b00-8000-000000000004'::uuid, 'new_promo', now()::timestamp, 2))
    INSERT INTO user_promo_codes (city, user_id, code, "timestamp", usage_count)
    (SELECT DISTINCT ON(city, user_id, code) * FROM inputrows)
    ON CONFLICT (city, user_id, code)
    DO NOTHING;

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:

> WITH inputrows (city, user_id, code, "timestamp", usage_count)
    AS (VALUES ('new york', '147ae147-ae14-4b00-8000-000000000004'::uuid, 'promo_code', now()::timestamp, 0), ('new york', '147ae147-ae14-4b00-8000-000000000004'::uuid, 'new_promo', now()::timestamp, 2))
    INSERT INTO user_promo_codes (city, user_id, code, "timestamp", usage_count)
    (SELECT DISTINCT ON(city, user_id, code) * FROM inputrows ORDER BY (city, user_id, code, usage_count))
    ON CONFLICT (city, user_id, code)
    DO NOTHING;

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.

​Required privileges

​Synopsis

​Parameters

​ON CONFLICT clause

​INSERT ON CONFLICT vs. UPSERT

​Performance best practices

​Bulk inserts

​Examples

​Setup

​Insert a single row

​Insert multiple rows into an existing table

​Insert from a SELECT statement

​Insert default values

​Insert and return values

​Update values ON CONFLICT

​Do not update values ON CONFLICT

​Import data containing duplicate rows using ON CONFLICT and DISTINCT ON

​See also

Required privileges

Synopsis

Parameters

`ON CONFLICT` clause

`INSERT ON CONFLICT` vs. `UPSERT`

Performance best practices

Bulk inserts

Examples

Setup

Insert a single row

Insert multiple rows into an existing table

Insert from a `SELECT` statement

Insert default values

Insert and return values

Update values `ON CONFLICT`

Do not update values `ON CONFLICT`

Import data containing duplicate rows using `ON CONFLICT` and `DISTINCT ON`

See also