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:
1 | cockroach start --insecure --listen-addr=localhost |
- Open another terminal and use the following command to start the CockroachDB SQL client.
1 | 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.
1 2 3 | 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:
1 | GRANT ALL ON DATABASE testdatabase TO raizel. |
Next, we’ll create a sample table using the following statement:
1 2 3 4 5 | 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:
1 | INSERT INTO tblrecords (id, name, age) VALUES (1, 'rommel', 37); |
The results you see will look like the following:
1 2 3 4 | -- inserted 1 row/record INSERT 1 -- processing time TIME: 12.078681ms |
To verify that our INSERT statement executed successfully, use the statement shown below:
1 | SELECT * FROM tblrecords |
The results that are returned should look like the following:
1 2 3 4 | id | name | age +----+--------+-----+ 1 | rommel | 37 (1 ROW) |
Another method of inserting a row into a CockroachDB table looks like this:
1 | 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:
1 2 3 4 5 | 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:
1 | INSERT INTO tblrecords (id, name, age) VALUES (3, 'raizel',15), (4, 'yeshua', 8),(5,'abi', 2); |
The output will look like this:
1 2 3 | 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:
1 2 3 4 5 6 7 8 | 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:
1 2 3 4 | 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 SELECT
ed from another table:
1 2 3 4 5 6 | -- 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:
1 2 | -- Select everything in tblrecords SELECT * FROM tblrecords |
The result should look like the following:
1 2 3 4 5 6 7 8 9 10 11 | 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.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started