This page has instructions for getting data into CockroachDB with various programming languages, using the INSERT
SQL statement.
Before you begin
Before reading this page, do the following:
- Install CockroachDB.
- Start a local cluster, or create a CockroachDB Cloud cluster.
- Install a Postgres client.
- Connect to the database.
Your application should use a retry loop to handle transaction errors that can occur under contention.
Insert rows
When inserting multiple rows, a single multi-row insert statement is faster than multiple single-row statements.
CREATE TABLE IF NOT EXISTS accounts (id INT PRIMARY KEY, balance INT);
INSERT INTO accounts (id, balance) VALUES (1, 1000), (2, 250);
For more information about how to use the built-in SQL client, see the cockroach sql
reference docs.
// 'db' is an open database connection
// Insert two rows into the "accounts" table.
if _, err := db.Exec(
"INSERT INTO accounts (id, balance) VALUES (1, 1000), (2, 250)"); err != nil {
log.Fatal(err)
}
For complete examples, see:
// ds is an org.postgresql.ds.PGSimpleDataSource
try (Connection connection = ds.getConnection()) {
connection.setAutoCommit(false);
PreparedStatement pstmt = connection.prepareStatement("INSERT INTO accounts (id, balance) VALUES (?, ?)");
pstmt.setInt(1, 1);
pstmt.setInt(2, 1000);
pstmt.addBatch();
pstmt.executeBatch();
connection.commit();
} catch (SQLException e) {
System.out.printf("sql state = [%s]\ncause = [%s]\nmessage = [%s]\n",
e.getSQLState(), e.getCause(), e.getMessage());
}
For complete examples, see:
# conn is a psycopg2 connection
with conn.cursor() as cur:
cur.execute('INSERT INTO accounts (id, balance) VALUES (1, 1000), (2, 250)')
conn.commit()
For complete examples, see:
Bulk insert
If you need to get a lot of data into a CockroachDB cluster quickly, use the IMPORT
statement instead of sending SQL INSERT
s from application code. It will be much faster because it bypasses the SQL layer altogether and writes directly to the data store using low-level commands. For instructions, see the Migration Overview.
See also
Reference information related to this task:
- Migration Overview
IMPORT
- Import performance
INSERT
UPSERT
- Understanding and Avoiding Transaction Contention
- Multi-row DML best practices
- Insert Multiple Rows
Other common tasks: