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:
1 2 3 4 5 | cockroach start-single-node \ --insecure \ --listen-addr=localhost:26257 \ --http-addr=localhost:8080 \ --background |
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 inlocalhost:26257
and for the user admin traffic port to pull the request by the default port oflocalhost: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:
1 | cockroach sql --insecure --host=localhost:26257 |
Now execute the following command to create the database:
1 | CREATE DATABASE demo; |
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:
1 2 3 | CREATE TABLE compensation( emp_id INT PRIMARY KEY, salary REAL); |
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:
1 2 3 4 5 6 | emp_id | salary +--------+--------+ 1 | 28200 2 | 30000 3 | 15700 (3 rows) |
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:
1 2 | UPSERT INTO compensation(emp_id, salary) VALUES (4, 25100); |
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:
1 2 3 4 5 6 7 8 9 10 11 | employee_id | salary +-------------+--------+ 1 | 28200 2 | 30000 3 | 15700 4 | 25100 5 | 21635 6 | 12200 7 | 28456 8 | 23000 (8 rows) |
Now execute the following command to UPSERT
a salary in the above table:
1 2 | UPSERT INTO compensation(emp_id, salary) VALUES (3, 29000), |
As shown below, the newly upserted value is now displayed in emp_id
row 3:
1 2 3 4 5 | SELECT * FROM compensation WHERE emp_id = 3; emp_id | salary +--------+--------+ 3 | 29000 (1 ROW) |
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.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started