The Use of Upsert in CockroachDB

Introduction

The UPSERT statement in CockroachDB is used similarly to the Insert on Conflict command, but the two have slightly different functions. Typically, CockroachDB returns an error when values conflict with a unique constraint in the columns of a table. However, the UPSERT statement is unique to primary-key columns, as it is used like an ID number and inserts rows in a table where the uniqueness constraints are not violated by the specified values. This tutorial will providing instructions on how the upsert in CockroachDB function works along with providing hands-on examples.

Prerequisites

• CockroachDB must be properly installed and configured on the local machine to use the upsert in CockroachDB function. Execute the cockroach version command to obtain the current CockroachDB version on a Linux operating system.

Upserting in CockroachDB

The UPSERT statement is unique to primary-key columns, as it is used like an ID number. While the UPSERT statement can be used similarly to the INSERT ON CONFLICT command, it has a slightly different function as the upsert command is more flexible and considered unique when used in various columns.

Create and start the local cluster in cockroachDB

This section will cover creating a sample database for demonstrating the UPSERT statement in the examples used in this tutorial. First, execute the following commands to start the local cluster:

Following is a breakdown of the flags used in the above commands for starting the local cluster:

• The --insecure flag is used to gain unencrypted access for the development of CockroachDB.

• The --listen-addr and --http-addr flag is used to instruct the node to connect using the port in localhost:26257 and for the user admin traffic port to pull the request by the default port of localhost:8080.

• The --background flag executes the command to take control of everything in the SQL built-in shell, once the node cluster is ready to accept the request.

Start the SQL built in shell for the creation of the database

Begin building the database by starting the built-in shell for CockroachDB with the following command:

Now execute the following command to create the database:

NOTE: When entering the database in CockroachDB, the USE command followed by the database name will allow access the specified database.

Now execute the following command to create a table:

If there is a value of rows inside the table, the SELECT * FROM compensation; command will display and return the table records, as shown here:

Upserting rows without conflict

The emp_id is the primary key of the table compensation. It can be inserted without conflict with any value that currently exists in the row, permitting the insertion of the new row in the table.

The UPSERT statement will now insert the following new row in the table:

NOTE: Multiple row values in the command can be upserted by adding a comma (,) after the parentheses.

Upserting rows in conflict with the primary key

For using the UPSERT statement in the emp_id, that has the primary key constraint with a nonunique value, the statement will update the row of the compensation table with the new salary.

The following table uses the SELECT * FROM compensation command:

Now execute the following command to UPSERT a salary in the above table:

As shown below, the newly upserted value is now displayed in emp_id row 3:

Conclusion

This tutorial explained how the upsert in CockroachDB function works and provided practice examples. The tutorial gave an overview of upserting in CockroachDB and specifically covered how to create and start the local cluster in CockroachDB and provided a breakdown of the command flags used for starting the local cluster. The article then explained how to create the database and table and how to add rows to that table with and without conflict with the primary key. Remember that when performing an upsert in CockroachDB, multiple row values can be upserted by placing a comma (,) into the command after the parentheses.