How do I bulk insert data into CockroachDB?
Currently, you can bulk insert data with batches of INSERT statements not exceeding a few MB. The size of your rows determines how many you can use, but 1,000 - 10,000 rows typically works best. For more details, see Import Data.
How do I auto-generate unique row IDs in CockroachDB?
To auto-generate unique row IDs, use the UUID column with the gen_random_uuid() function as the default value:
> CREATE TABLE t1 (id UUID PRIMARY KEY DEFAULT gen_random_uuid(), name STRING);
> INSERT INTO t1 (name) VALUES ('a'), ('b'), ('c');
> SELECT * FROM t1;
+--------------------------------------+------+
| id | name |
+--------------------------------------+------+
| 60853a85-681d-4620-9677-946bbfdc8fbc | c |
| 77c9bc2e-76a5-4ebc-80c3-7ad3159466a1 | b |
| bd3a56e1-c75e-476c-b221-0da9d74d66eb | a |
+--------------------------------------+------+
(3 rows)
Alternatively, you can use the BYTES column with the uuid_v4() function as the default value instead:
> CREATE TABLE t2 (id BYTES PRIMARY KEY DEFAULT uuid_v4(), name STRING);
> INSERT INTO t2 (name) VALUES ('a'), ('b'), ('c');
> SELECT * FROM t2;
+---------------------------------------------------+------+
| id | name |
+---------------------------------------------------+------+
| "\x9b\x10\xdc\x11\x9a\x9cGB\xbd\x8d\t\x8c\xf6@vP" | a |
| "\xd9s\xd7\x13\n_L*\xb0\x87c\xb6d\xe1\xd8@" | c |
| "\uac74\x1dd@B\x97\xac\x04N&\x9eBg\x86" | b |
+---------------------------------------------------+------+
(3 rows)
In either case, generated IDs will be 128-bit, large enough for there to be virtually no chance of generating non-unique values. Also, once the table grows beyond a single key-value range (more than 64MB by default), new IDs will be scattered across all of the table's ranges and, therefore, likely across different nodes. This means that multiple nodes will share in the load.
If it's important for generated IDs to be stored in the same key-value range, you can use the SERIAL data type, which is an alias for INT with the unique_rowid() function as the default value:
> CREATE TABLE t3 (id SERIAL PRIMARY KEY, name STRING);
> INSERT INTO t3 (name) VALUES ('a'), ('b'), ('c');
> SELECT * FROM t3;
+--------------------+------+
| id | name |
+--------------------+------+
| 293807573840855041 | a |
| 293807573840887809 | b |
| 293807573840920577 | c |
+--------------------+------+
(3 rows)
On insert, the unique_rowid() function generates a default value from the timestamp and ID of the node executing the insert. Such time-ordered values are likely to be globally unique except in cases where a very large number of IDs (100,000+) are generated per node per second.
How do I get the last ID/SERIAL value inserted into a table?
There’s no function in CockroachDB for returning last inserted values, but you can use the RETURNING clause of the INSERT statement.
For example, this is how you’d use RETURNING to return an auto-generated SERIAL value:
> CREATE TABLE users (id SERIAL, name STRING);
> INSERT INTO users (name) VALUES ('mike') RETURNING id;
Does CockroachDB support JOIN?
CockroachDB has basic, non-optimized support for SQL JOIN, whose performance we're working to improve.
To learn more, see our blog posts on CockroachDB's JOINs: - Modesty in Simplicity: CockroachDB's JOIN. - On the Way to Better SQL Joins
When should I use interleaved tables?
Interleaving tables improves query performance by optimizing the key-value structure of closely related tables, attempting to keep data on the same key-value range if it's likely to be read and written together.
You're most likely to benefit from interleaved tables when:
- Your tables form a hierarchy
- Queries maximize the benefits of interleaving
- Queries do not suffer too greatly from interleaving's tradeoffs
Does CockroachDB support JSON or Protobuf datatypes?
Not currently, but we plan to offer JSON/Protobuf datatypes.
How do I know which index CockroachDB will select for a query?
To see which indexes CockroachDB is using for a given query, you can use the EXPLAIN statement, which will print out the query plan, including any indexes that are being used:
> EXPLAIN SELECT col1 FROM tbl1;
If you'd like to tell the query planner which index to use, you can do so via some special syntax for index hints:
> SELECT col1 FROM tbl1@idx1;
How do I log SQL queries?
For production clusters, the best way to log queries is to turn on the cluster-wide setting sql.trace.log_statement_execute:
> SET CLUSTER SETTING sql.trace.log_statement_execute = true;
With this setting on, each node of the cluster writes all SQL queries it executes to its log file. When you no longer need to log queries, you can turn the setting back off:
> SET CLUSTER SETTING sql.trace.log_statement_execute = false;
Alternatively, if you are testing CockroachDB locally and want to log queries executed just by a specific node, you can pass --vmodule=executor=2 to the cockroach start command when starting the node. For example, to start a single node locally and log all SQL queries it executes, you'd run:
$ cockroach start --insecure --host=localhost --vmodule=executor=2
Does CockroachDB support a UUID type?
Yes. For more details, see UUID.
How does CockroachDB sort results when ORDER BY is not used?
When an ORDER BY clause is not used in a SELECT query, retrieved rows are not sorted by any consistent criteria. Instead, CockroachDB returns them as the coordinating node receives them.
Why are my INT columns returned as strings in JavaScript?
In CockroachDB, all INTs are represented with 64 bits of precision, but JavaScript numbers only have 53 bits of precision. This means that large integers stored in CockroachDB are not exactly representable as JavaScript numbers. For example, JavaScript will round the integer 235191684988928001 to the nearest representable value, 235191684988928000. Notice that the last digit is different. This is particularly problematic when using the unique_rowid() function, since unique_rowid() nearly always returns integers that require more than 53 bits of precision to represent.
To avoid this loss of precision, Node's pg driver will, by default, return all CockroachDB INTs as strings.
// Schema: CREATE TABLE users (id INT DEFAULT unique_rowid(), name STRING);
pgClient.query("SELECT id FROM users WHERE name = 'Roach' LIMIT 1", function(err, res) {
var idString = res.rows[0].id;
// idString === '235191684988928001'
// typeof idString === 'string'
});
To perform another query using the value of idString, you can simply use idString directly, even where an INT type is expected. The string will automatically be coerced into a CockroachDB INT.
pgClient.query("UPDATE users SET name = 'Ms. Roach' WHERE id = $1", [idString], function(err, res) {
// All should be well!
});
If you instead need to perform arithmetic on INTs in JavaScript, you will need to use a big integer library like Long.js. Do not use the built-in parseInt function.
parseInt(idString, 10) + 1; // WRONG: returns 235191684988928000
require('long').fromString(idString).add(1).toString(); // GOOD: returns '235191684988928002'
See Also
Was this helpful?
