## Synopsis Use the INSERT statement to add one or more rows to the specified table. ## Syntax {{%ebnf%}} insert, returning_clause, column_values, conflict_target, conflict_action {{%/ebnf%}} See the section [The WITH clause and common table expressions](../../with-clause/) for more information about the semantics of the `common_table_expression` grammar rule. ## Semantics Constraints must be satisfied. ### *insert* ### *table_name* Specify the name of the table. If the specified table does not exist, an error is raised. ### *column_names* Specify a comma-separated list of columns names. If a specified column does not exist, an error is raised. Each of the primary key columns must have a non-null value. ### OVERRIDING SYSTEM VALUE When you provide this clause, any values provided for identity columns will override the default sequence-generated values. If an identity column is defined as `GENERATED ALWAYS`, it will raise an error to insert an explicit value (other than `DEFAULT`) without setting either `OVERRIDING SYSTEM VALUE` or `OVERRIDING USER VALUE` clauses. (When an identity column is defined as `GENERATED BY DEFAULT`, `OVERRIDING SYSTEM VALUE` will be the normal behavior and specifying it does nothing, but YugabyteDB allows it as an extension.) ### OVERRIDING USER VALUE When you provide this clause, any values provided for identity columns is ignored, and the default sequence-generated values will be applied. This clause is helpful when copying values between tables. Writing `INSERT INTO table1 OVERRIDING USER VALUE SELECT * FROM table0` copies all non-identity columns from `table0` to `table1`, while the values of the identity columns in `table1` is generated by the sequences associated with `table1`. ### VALUES clause - Each of the values list must have the same length as the columns list. - Each value must be convertible to its corresponding (by position) column type. - Each value literal can be an expression. ### ON CONFLICT clause - The target table must have at least one column (list) with either a unique index or a unique constraint. We shall refer to this as a unique key. The argument of VALUES is a relation that must include at least one of the target table's unique keys. Some of the values of this unique key might be new, and others might already exist in the target table. - The basic aim of INSERT ON CONFLICT is to insert the rows with new values of the unique key and to update the rows with existing values of the unique key to set the values of the remaining specified columns to those in the VALUES relation. In this way, the net effect is either to insert or to update; and for this reason the INSERT ON CONFLICT variant is often colloquially referred to as "upsert". To optimize performance, you can set the [yb_insert_on_conflict_read_batch_size](../../../../../reference/configuration/yb-tserver/#yb-insert-on-conflict-read-batch-size) configuration parameter to batch upserts. This reduces the number of network round trips required compared to performing the operations serially. ### *returning_clause* The optional `RETURNING` clause causes `INSERT` to compute and return values based on each row that's actually inserted or updated (when you use an `ON CONFLICT DO UPDATE` clause). You'll primarily find this useful for getting values supplied by defaults, like a serial sequence number. However, you can use any expression that uses the table's columns. The syntax for the `RETURNING` list is identical to the output list of `SELECT`. Only rows that are successfully inserted or updated are returned. For example, if a row is locked but not updated because an `ON CONFLICT DO UPDATE ... WHERE` clause condition wasn't satisfied, that row won't be returned. ### *column_values* The values you supply in the `VALUES` clause or query are matched with your column list from left to right. Any column not included in your explicit or implicit column list is filled with its default value. If there's no declared default value for that column, it is set to `NULL`. If the expression for any column isn't the correct data type, YugabyteDB will attempt automatic type conversion. ### *conflict_target* The `conflict_target` specifies which conflicts `ON CONFLICT` should handle by choosing arbiter indexes. You can either perform unique index inference, or name a constraint explicitly. The requirements depend on which conflict action you're using: - **For `ON CONFLICT DO NOTHING`** - Specifying a `conflict_target` is optional. When you omit it, conflicts with all usable constraints and unique indexes are handled. - **For `ON CONFLICT DO UPDATE`** - You must provide a `conflict_target`. ### *conflict_action* The `conflict_action` specifies what to do when a conflict occurs with `ON CONFLICT`. You have the following two options: - `DO NOTHING` - Simply ignore the conflicting row and don't insert it. - `DO UPDATE` - Perform an update operation on the existing row instead. When you use `DO UPDATE`, you'll need to specify the exact details of the `UPDATE` action. In the `SET` and `WHERE` clauses of `ON CONFLICT DO UPDATE`, you can access: - The existing row using the table's name (or an alias you've defined). - The row that was proposed for insertion using the special `excluded` table. You'll need `SELECT` privilege on any column in the target table where you're reading from the corresponding `excluded` columns. Note that the `excluded` values reflect all the effects of per-row `BEFORE INSERT` triggers, as those effects may have contributed to the row being excluded from insertion. #### *update_item* #### *condition* ### Compatibility The SQL standard specifies that OVERRIDING SYSTEM VALUE can only be specified if an identity column that is generated always exists. YugabyteDB allows the clause in any case and ignores it if it is not applicable. ## Examples First, the bare insert. Create a sample table. ```plpgsql yugabyte=# CREATE TABLE sample(k1 int, k2 int, v1 int, v2 text, PRIMARY KEY (k1, k2)); ``` Insert some rows. ```plpgsql yugabyte=# INSERT INTO sample VALUES (1, 2.0, 3, 'a'), (2, 3.0, 4, 'b'), (3, 4.0, 5, 'c'); ``` Check the inserted rows. ```plpgsql yugabyte=# SELECT * FROM sample ORDER BY k1; ``` ```output k1 | k2 | v1 | v2 ----+----+----+---- 1 | 2 | 3 | a 2 | 3 | 4 | b 3 | 4 | 5 | c ``` Next, a basic "upsert" example. Re-create and re-populate the sample table. ```plpgsql yugabyte=# DROP TABLE IF EXISTS sample CASCADE; ``` ```plpgsql yugabyte=# CREATE TABLE sample( id int CONSTRAINT sample_id_pk PRIMARY KEY, c1 text CONSTRAINT sample_c1_NN NOT NULL, c2 text CONSTRAINT sample_c2_NN NOT NULL); ``` ```plpgsql yugabyte=# INSERT INTO sample(id, c1, c2) VALUES (1, 'cat' , 'sparrow'), (2, 'dog' , 'blackbird'), (3, 'monkey' , 'thrush'); ``` Check the inserted rows. ```plpgsql yugabyte=# SELECT id, c1, c2 FROM sample ORDER BY id; ``` ```output id | c1 | c2å ----+--------+----------- 1 | cat | sparrow 2 | dog | blackbird 3 | monkey | thrush ``` Demonstrate "on conflict do nothing". In this case, you don't need to specify the conflict target. ```plpgsql yugabyte=# INSERT INTO sample(id, c1, c2) VALUES (3, 'horse' , 'pigeon'), (4, 'cow' , 'robin') ON CONFLICT DO NOTHING; ``` Check the result. The non-conflicting row with id = 4 is inserted, but the conflicting row with id = 3 is NOT updated. ```plpgsql yugabyte=# SELECT id, c1, c2 FROM sample ORDER BY id; ``` ```output id | c1 | c2 ----+--------+----------- 1 | cat | sparrow 2 | dog | blackbird 3 | monkey | thrush 4 | cow | robin ``` Demonstrate the real "upsert". In this case, you DO need to specify the conflict target. Notice the use of the EXCLUDED keyword to specify the conflicting rows in the to-be-upserted relation. ```plpgsql yugabyte=# INSERT INTO sample(id, c1, c2) VALUES (3, 'horse' , 'pigeon'), (5, 'tiger' , 'starling') ON CONFLICT (id) DO UPDATE SET (c1, c2) = (EXCLUDED.c1, EXCLUDED.c2); ``` Check the result. The non-conflicting row with id = 5 is inserted, and the conflicting row with id = 3 is updated. ```plpgsql yugabyte=# SELECT id, c1, c2 FROM sample ORDER BY id; ``` ```output id | c1 | c2 ----+-------+----------- 1 | cat | sparrow 2 | dog | blackbird 3 | horse | pigeon 4 | cow | robin 5 | tiger | starling ``` We can make the "update" happen only for a specified subset of the excluded rows. We illustrate this by attempting to insert two conflicting rows (with id = 4 and id = 5) and one non-conflicting row (with id = 6). And you specify that the existing row with c1 = 'tiger' should not be updated with "WHERE sample.c1 <> 'tiger'". ```plpgsql INSERT INTO sample(id, c1, c2) VALUES (4, 'deer' , 'vulture'), (5, 'lion' , 'hawk'), (6, 'cheeta' , 'chaffinch') ON CONFLICT (id) DO UPDATE SET (c1, c2) = (EXCLUDED.c1, EXCLUDED.c2) WHERE sample.c1 <> 'tiger'; ``` Check the result. The non-conflicting row with id = 6 is inserted; the conflicting row with id = 4 is updated; but the conflicting row with id = 5 (and c1 = 'tiger') is NOT updated; ```plpgsql yugabyte=# SELECT id, c1, c2 FROM sample ORDER BY id; ``` ```output id | c1 | c2 ----+--------+----------- 1 | cat | sparrow 2 | dog | blackbird 3 | horse | pigeon 4 | deer | vulture 5 | tiger | starling 6 | cheeta | chaffinch ``` Notice that this restriction is legal too: ```sql WHERE EXCLUDED.c1 <> 'lion' ``` Finally, a slightly more elaborate "upsert" example. Re-create and re-populate the sample table. Notice that id is a self-populating surrogate primary key and that c1 is a business unique key. ```plpgsql yugabyte=# DROP TABLE IF EXISTS sample CASCADE; ``` ```plpgsql CREATE TABLE sample( id INTEGER GENERATED ALWAYS AS IDENTITY CONSTRAINT sample_id_pk PRIMARY KEY, c1 TEXT CONSTRAINT sample_c1_NN NOT NULL CONSTRAINT sample_c1_unq unique, c2 TEXT CONSTRAINT sample_c2_NN NOT NULL); ``` ```plpgsql INSERT INTO sample(c1, c2) VALUES ('cat' , 'sparrow'), ('deer' , 'thrush'), ('dog' , 'blackbird'), ('horse' , 'vulture'); ``` Check the inserted rows. ```plpgsql yugabyte=# SELECT id, c1, c2 FROM sample ORDER BY c1; ``` ```output id | c1 | c2 ----+-------+----------- 1 | cat | sparrow 2 | deer | thrush 3 | dog | blackbird 4 | horse | vulture ``` Now do the upsert. Notice that this illustrates the usefulness of the WITH clause to define the to-be-upserted relation before the INSERT clause and use a subselect instead of a VALUES clause. We also specify the conflict columns indirectly by mentioning the name of the unique constrained that covers them. ```plpgsql yugabyte=# WITH to_be_upserted AS ( SELECT c1, c2 FROM (VALUES ('cat' , 'chaffinch'), ('deer' , 'robin'), ('lion' , 'duck'), ('tiger' , 'pigeon') ) AS t(c1, c2) ) INSERT INTO sample(c1, c2) SELECT c1, c2 FROM to_be_upserted ON CONFLICT ON CONSTRAINT sample_c1_unq DO UPDATE SET c2 = EXCLUDED.c2; ``` Check the inserted rows. ```plpgsql yugabyte=# SELECT id, c1, c2 FROM sample ORDER BY c1; ``` ```output id | c1 | c2 ----+-------+----------- 1 | cat | chaffinch 2 | deer | robin 3 | dog | blackbird 4 | horse | vulture 7 | lion | duck 8 | tiger | pigeon ``` ## See also - [COPY](../cmd_copy) - [CREATE TABLE](../ddl_create_table) - [SELECT](../dml_select/)