How To Perform an Insert in CockroachDB

Introduction

If you’re just getting started with CockroachDB, it’s important to familiarize yourself with all the basic operations. One common task that you’ll find yourself performing again and again is the insert operation. Fortunately, inserting a record is a quick and easy task, especially if you have some knowledge of SQL. In this article, we’ll provide step-by-step instructions for inserting a record in CockroachDB.

Prerequisites

Before we begin looking at the SQL statements needed to insert a record, let’s take a moment to discuss the system requirements for this task. There are a few important prerequisites to consider:

  • You’ll need to make sure that CockroachDB is properly installed and configured beforehand.

  • You’ll also need to ensure that the CockroachDB service is running in the background. To check whether the service is running, use the following command:

cockroach start --insecure --listen-addr=localhost
  • Open another terminal and use the following command to start the CockroachDB SQL client.
cockroach sql --insecure --host=localhost:26257
  • It’s helpful to have some basic knowledge of SQL syntax in order to follow along with the examples presented in this article.

Creating The Sample Database

Let’s begin by creating a sample user and database that we’ll use in this tutorial. You can use the following statements to create the raizel user and testdatabase database.

CREATE USER IF NOT EXISTS raizel;

CREATE DATABASE testdatabase;

For the purposes of this tutorial, we’ll give the user read and write permission to the database:

GRANT ALL ON DATABASE testdatabase TO raizel.

Next, we’ll create a sample table using the following statement:

CREATE TABLE tblrecords(
id INT PRIMARY KEY,
name STRING,
age INT
);

NOTE: Whenever you want to exit the SQL shell, you can simply type \q.

Inserting a Single row In CockroachDB

The SQL statement shown below will insert a single row into a table in a CockroachDB database:

INSERT INTO tblrecords (id, name, age) VALUES (1, 'rommel', 37);

The results you see will look like the following:

-- inserted 1 row/record
INSERT 1
-- processing time
TIME: 12.078681ms

To verify that our INSERT statement executed successfully, use the statement shown below:

SELECT * FROM tblrecords

The results that are returned should look like the following:

id | name | age
+----+--------+-----+
1 | rommel | 37
(1 ROW)

Another method of inserting a row into a CockroachDB table looks like this:

INSERT INTO tblrecords VALUES (1, 'rommel', 37);

NOTE: In the above SQL statement, the table’s column names were omitted. Therefore, you must make sure to list the values that are to be inserted in the declared order of the table columns.

Like we did in the previous example, we can verify that the insert was successful by using the SELECT command. The results should look like the following:

id | name | age
+----+--------+-----+
1 | rommel | 37
2 | risa | 37
(2 ROWS)

Inserting Multiple Rows into an Existing table in CockroachDB

Now that we’ve learned how to insert a single row, let’s try an example where we insert multiple rows. The SQL statement shown below will insert multiple rows into a table in a CockroachDB database.

A multi-row insert process is faster than multiple single-row INSERT statements:

INSERT INTO tblrecords (id, name, age) VALUES (3, 'raizel',15), (4, 'yeshua', 8),(5,'abi', 2);

The output will look like this:

INSERT 3

TIME: 9.395696ms

If you’d like to verify that the operation was a success, you can use the same SQL statement SELECT * FROM tblrecords that we used before. The results will look like the following:

id | name | age
+----+--------+-----+
1 | rommel | 37
2 | risa | 37
3 | raizel | 15
4 | yeshua | 8
5 | abi | 2
(5 ROWS)

Inserting a row using SELECT Statement in CockroachDB

For our next example, we’ll need to create another sample table:

CREATE TABLE other_tblrecords (
id INT PRIMARY KEY,
rating INT
);

Now we’ll perform an INSERT where values being inserted into one table are being SELECTed from another table:

-- Insert row in other_tblrecords table
INSERT INTO other_tblrecords (id, name, age) VALUES (6, ivan, 8), (7, yvonne, 10), (8, anne, 15);

-- Insert new rows in tblrecords where values is coming from other_tblrecords
--with ids having a value greater than 5
INSERT INTO tblrecords (id, name, age) SELECT id, name, age FROM other_tblrecords WHERE id > 5;

You can use the SQL statement shown below to verify that the insert process was successful:

-- Select everything in tblrecords
SELECT * FROM tblrecords

The result should look like the following:

id | name | age
+----+--------+-----+
1 | rommel | 37
2 | risa | 37
3 | raizel | 15
4 | yeshua | 8
5 | abi | 2
6 | ivan | 8
7 | yvonne | 10
8 | anne | 15
(8 ROWS)

Conclusion

If you’re using CockroachDB to store and manage data, it’s important to know how to perform basic operations such as inserting records. As you can see from the examples, performing an insert in CockroachDB is a simple and straightforward task. Using the instructions provided in this article, you’ll have no trouble inserting records in CockroachDB.

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.