> ## Documentation Index
> Fetch the complete documentation index at: https://cockroachlabs.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# SQL FAQs

export const InternalLink = ({version, path = "", children, ...props}) => {
  let detectedVersion = version || "stable";
  if (typeof window !== 'undefined' && !version) {
    const match = window.location.pathname.match(/\/docs\/([^/]+)/);
    if (match) {
      detectedVersion = match[1];
    }
  }
  const normalizedPath = path.startsWith("/") ? path.slice(1) : path;
  return <a href={`/docs/${detectedVersion}/${normalizedPath}`} {...props}>
      {children}
    </a>;
};

## How do I bulk insert data into CockroachDB?

* To bulk-insert data into an existing table, batch multiple rows in one <InternalLink path="insert#insert-multiple-rows-into-an-existing-table">multi-row `INSERT`</InternalLink> statement and do not include the `INSERT` statements within a transaction. Experimentally determine the optimal batch size for your application by monitoring the performance for different batch sizes (10 rows, 100 rows, 1000 rows).

<Note>
  You can also use the <InternalLink path="import-into">`IMPORT INTO`</InternalLink> statement to bulk-insert CSV data into an existing table.
</Note>

## How do I auto-generate unique row IDs in CockroachDB?

To auto-generate unique row identifiers, you can use the following <InternalLink path="functions-and-operators">functions</InternalLink>:

* [Use `gen_random_uuid()`](#use-gen_random_uuid): Generates a UUIDv4 with `UUID` data type.
* [Use `uuid_v4()`](#use-uuid_v4): Generates a UUIDv4 with `BYTES` data type.
* [Use `unique_rowid()`](#use-unique_rowid): Generates a globally unique `INT` data type

For performance reasons, if you are going to use UUIDs, Cockroach Labs strongly recommends using **UUIDv4** as defined by [RFC 4122](https://www.ietf.org/rfc/rfc4122.txt). This is the format generated by the <InternalLink path="functions-and-operators">`gen_random_uuid()` and `uuid_v4()` built-in functions</InternalLink>. Other types of UUID are largely untested with CockroachDB and will require performance testing to avoid hotspots<InternalLink path="performance-best-practices-overview#hotspots">hotspots</InternalLink>.

#### Use `gen_random_uuid()`

To use the <InternalLink path="uuid">`UUID`</InternalLink> column with the `gen_random_uuid()` <InternalLink path="functions-and-operators">function</InternalLink> as the <InternalLink path="default-value">default value</InternalLink>:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE TABLE users (
    id UUID NOT NULL DEFAULT gen_random_uuid(),
    city STRING NOT NULL,
    name STRING NULL,
    address STRING NULL,
    credit_card STRING NULL,
    CONSTRAINT "primary" PRIMARY KEY (city ASC, id ASC),
    FAMILY "primary" (id, city, name, address, credit_card)
);
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
INSERT INTO users (name, city) VALUES ('Petee', 'new york'), ('Eric', 'seattle'), ('Dan', 'seattle');
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SELECT * FROM users;
```

```
                   id                  |   city   | name  | address | credit_card
+--------------------------------------+----------+-------+---------+-------------+
  cf8ee4e2-cd74-449a-b6e6-a0fb2017baa4 | new york | Petee | NULL    | NULL
  2382564e-702f-42d9-a139-b6df535ae00a | seattle  | Eric  | NULL    | NULL
  7d27e40b-263a-4891-b29b-d59135e55650 | seattle  | Dan   | NULL    | NULL
(3 rows)
```

For performance, CockroachDB defaults to skipping uniqueness checks for `gen_random_uuid()` due to the near-zero probability of UUID collisions. UUID uniqueness checks can be enabled by setting the <InternalLink path="cluster-settings">`sql.optimizer.uniqueness_checks_for_gen_random_uuid.enabled`</InternalLink> cluster setting to `true`.

#### Use `uuid_v4()`

Alternatively, you can use the <InternalLink path="bytes">`BYTES`</InternalLink> column with the `uuid_v4()` function as the default value:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE TABLE users2 (
    id BYTES DEFAULT uuid_v4(),
    city STRING NOT NULL,
    name STRING NULL,
    address STRING NULL,
    credit_card STRING NULL,
    CONSTRAINT "primary" PRIMARY KEY (city ASC, id ASC),
    FAMILY "primary" (id, city, name, address, credit_card)
);
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
INSERT INTO users2 (name, city) VALUES ('Anna', 'new york'), ('Jonah', 'seattle'), ('Terry', 'chicago');
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SELECT * FROM users;
```

```
                        id                       |   city   | name  | address | credit_card
+------------------------------------------------+----------+-------+---------+-------------+
  4\244\277\323/\261M\007\213\275*\0060\346\025z | chicago  | Terry | NULL    | NULL
  \273*t=u.F\010\274f/}\313\332\373a             | new york | Anna  | NULL    | NULL
  \004\\\364nP\024L)\252\364\222r$\274O0         | seattle  | Jonah | NULL    | NULL
(3 rows)
```

In either case, generated IDs will be 128-bit, sufficiently large to generate unique values. Once the table grows beyond a single key-value range's <InternalLink path="configure-replication-zones">default size</InternalLink>, 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.

This approach has the disadvantage of creating a primary key that may not be useful in a query directly, which can require a join with another table or a secondary index.

#### Use `unique_rowid()`

If it is important for generated IDs to be stored in the same key-value range, you can use an <InternalLink path="int">integer type</InternalLink> with the `unique_rowid()` <InternalLink path="functions-and-operators">function</InternalLink> as the default value, either explicitly or via the <InternalLink path="serial">`SERIAL` pseudo-type</InternalLink>:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE TABLE users3 (
    id INT DEFAULT unique_rowid(),
    city STRING NOT NULL,
    name STRING NULL,
    address STRING NULL,
    credit_card STRING NULL,
    CONSTRAINT "primary" PRIMARY KEY (city ASC, id ASC),
    FAMILY "primary" (id, city, name, address, credit_card)
);
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
INSERT INTO users3 (name, city) VALUES ('Blake', 'chicago'), ('Hannah', 'seattle'), ('Bobby', 'seattle');
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SELECT * FROM users3;
```

```
          id         |  city   |  name  | address | credit_card
+--------------------+---------+--------+---------+-------------+
  469048192112197633 | chicago | Blake  | NULL    | NULL
  469048192112263169 | seattle | Hannah | NULL    | NULL
  469048192112295937 | seattle | Bobby  | NULL    | NULL
(3 rows)
```

Upon insert or upsert, 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. Also, there can be gaps and the order is not completely guaranteed.

In multi-region deployments with <InternalLink path="table-localities#regional-by-row-tables">`REGIONAL BY ROW`</InternalLink> tables, uniqueness checks can add latency due to cross-partition validation. You can improve performance by setting the `skip_unique_checks` index storage parameter to `true`, but this is **only** recommended if the application can guarantee uniqueness. For more information, refer to <InternalLink path="with-storage-parameter">`skip_unique_checks`</InternalLink>.

To understand the differences between the `UUID` and `unique_rowid()` options, see the <InternalLink path="sql-faqs#what-are-the-differences-between-uuid-sequences-and-unique_rowid">SQL FAQs</InternalLink>. For further background on UUIDs, see [What is a UUID, and Why Should You Care?](https://www.cockroachlabs.com/blog/what-is-a-uuid/).

## How do I generate unique, slowly increasing sequential numbers in CockroachDB?

Sequential numbers can be generated in CockroachDB using the `unique_rowid()` built-in function or using <InternalLink path="create-sequence">SQL sequences</InternalLink>. However, note the following considerations:

* Unless you need roughly-ordered numbers, use <InternalLink path="uuid">`UUID`</InternalLink> values instead. See the [previous
  FAQ](#how-do-i-auto-generate-unique-row-ids-in-cockroachdb) for details.
* <InternalLink path="create-sequence">Sequences</InternalLink> produce **unique** values. However, not all values are guaranteed to be produced (e.g., when a transaction is canceled after it consumes a value) and the values may be slightly reordered (e.g., when a transaction that
  consumes a lower sequence number commits after a transaction that consumes a higher number).
* For maximum performance, avoid using sequences or `unique_rowid()` to generate row IDs or indexed columns. Values generated in these ways are logically close to each other and can cause [contention](performance-best-practices-overview.html#understanding-and-avoiding-transaction-contention) on a few data ranges during inserts. Instead, prefer <InternalLink path="uuid">`UUID`</InternalLink> identifiers.
* We <InternalLink path="schema-design-indexes#best-practices">discourage indexing on sequential keys</InternalLink>. If a table **must** be indexed on sequential keys, use <InternalLink path="hash-sharded-indexes">hash-sharded indexes</InternalLink>. Hash-sharded indexes distribute sequential traffic uniformly across ranges, eliminating single-range <InternalLink path="understand-hotspots">hotspots</InternalLink> and improving write performance on sequentially-keyed indexes at a small cost to read performance.

## What are the differences between `UUID`, sequences, and `unique_rowid()`?

| Property                             | <InternalLink path="uuid">UUID</InternalLink> generated with `uuid_v4()` | INT generated with `unique_rowid()`           | Sequences                                                                                                                    |
| ------------------------------------ | ------------------------------------------------------------------------ | --------------------------------------------- | ---------------------------------------------------------------------------------------------------------------------------- |
| Size                                 | 16 bytes                                                                 | 8 bytes                                       | 1 to 8 bytes                                                                                                                 |
| Ordering properties                  | Unordered                                                                | Highly time-ordered                           | Highly time-ordered                                                                                                          |
| Performance cost at generation       | Small, scalable                                                          | Small, scalable                               | Variable, can cause [contention](performance-best-practices-overview.html#understanding-and-avoiding-transaction-contention) |
| Value distribution                   | Uniformly distributed (128 bits)                                         | Contains time and space (node ID) components  | Dense, small values                                                                                                          |
| Data locality                        | Maximally distributed                                                    | Values generated close in time are co-located | Highly local                                                                                                                 |
| `INSERT` latency when used as key    | Small, insensitive to concurrency                                        | Small, but increases with concurrent INSERTs  | Higher                                                                                                                       |
| `INSERT` throughput when used as key | Highest                                                                  | Limited by max throughput on 1 node           | Limited by max throughput on 1 node                                                                                          |
| Read throughput when used as key     | Highest (maximal parallelism)                                            | Limited                                       | Limited                                                                                                                      |

## How do I order writes to a table to closely follow time in CockroachDB?

Most use cases that ask for a strong time-based write ordering can be solved with other, more distribution-friendly
solutions instead. For example, CockroachDB's [time travel queries (`AS OF SYSTEM
TIME`)](https://www.cockroachlabs.com/blog/time-travel-queries-select-witty_subtitle-the_future/) support the following:

* Paginating through all the changes to a table or dataset
* Determining the order of changes to data over time
* Determining the state of data at some point in the past
* Determining the changes to data between two points of time

Consider also that the values generated by `unique_rowid()`, described in the previous FAQ entries, also provide an approximate time ordering.

However, if your application absolutely requires strong time-based write ordering, it is possible to create a strictly monotonic counter in CockroachDB that increases over time as follows:

* Initially: `CREATE TABLE cnt(val INT PRIMARY KEY); INSERT INTO cnt(val) VALUES(1);`
* In each transaction: `INSERT INTO cnt(val) SELECT max(val)+1 FROM cnt RETURNING val;`

This will cause <InternalLink path="insert">`INSERT`</InternalLink> transactions to conflict with each other and effectively force the transactions to commit one at a time throughout the cluster, which in turn guarantees the values generated in this way are strictly increasing over time without gaps. The caveat is that performance is severely limited as a result.

If you find yourself interested in this problem, please <InternalLink path="support-resources">contact us</InternalLink> and describe your situation. We would be glad to help you find alternative solutions and possibly extend CockroachDB to better match your needs.

## 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 <InternalLink path="insert#insert-and-return-values">`RETURNING` clause</InternalLink> of the `INSERT` statement.

For example, this is how you’d use `RETURNING` to return a value auto-generated via `unique_rowid()` or <InternalLink path="serial">`SERIAL`</InternalLink>:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE TABLE users (id INT DEFAULT unique_rowid(), name STRING);
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> INSERT INTO users (name) VALUES ('mike') RETURNING id;
```

## What is transaction contention?

Transaction contention occurs when transactions issued from multiple clients at the same time
operate on the same data. This can cause transactions to wait on each other and decrease
performance, like when many people try to check out with the same cashier at a store.

For more information about contention, see <InternalLink path="performance-best-practices-overview#transaction-contention">Transaction Contention</InternalLink>.

## Does CockroachDB support `JOIN`?

<InternalLink path="joins">CockroachDB supports SQL joins</InternalLink>.

## Does CockroachDB support JSON or Protobuf datatypes?

Yes, the <InternalLink path="jsonb">`JSONB`</InternalLink> data type is supported.

## 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 <InternalLink path="explain">`EXPLAIN`</InternalLink> statement, which will print out the query plan, including any indexes that are being used:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> EXPLAIN SELECT col1 FROM tbl1;
```

If you'd like to tell the query planner which index to use, you can do so via some <InternalLink path="table-expressions#force-index-selection">special syntax for index hints</InternalLink>:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT col1 FROM tbl1@idx1;
```

## How do I log SQL queries?

You can enable the CockroachDB <InternalLink path="logging-overview#logging-channels">logging channels</InternalLink> that record SQL events.

## Does CockroachDB support a UUID type?

Yes. For more details, see <InternalLink path="uuid">`UUID`</InternalLink>.

## How does CockroachDB sort results when `ORDER BY` is not used?

When an <InternalLink path="order-by">`ORDER BY`</InternalLink> clause is not used in a query, rows are processed or returned in a
non-deterministic order. "Non-deterministic" means that the actual order
can depend on the logical plan, the order of data on disk, the topology
of the CockroachDB cluster, and is generally variable over time.

## Why are my `INT` columns returned as strings in JavaScript?

In CockroachDB, all `INT`s 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()` <InternalLink path="functions-and-operators">function</InternalLink>, 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](https://github.com/brianc/node-postgres) will, by default, return all CockroachDB `INT`s as strings.

```javascript theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
// 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`.

```javascript theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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 `INT`s in JavaScript, you will need to use a big integer library like [Long.js](https://www.npmjs.com/package/long). Do *not* use the built-in `parseInt` function.

```javascript theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
parseInt(idString, 10) + 1; // WRONG: returns 235191684988928000
require('long').fromString(idString).add(1).toString(); // GOOD: returns '235191684988928002'
```

## Can I use CockroachDB as a key-value store?

CockroachDB is a distributed SQL database built on a transactional and strongly-consistent key-value store. Although it is not possible to access the key-value store directly, you can mirror direct access using a "simple" table of two columns, with one set as the primary key:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE TABLE kv (k INT PRIMARY KEY, v BYTES);
```

When such a "simple" table has no indexes or foreign keys, <InternalLink path="insert">`INSERT`</InternalLink>/<InternalLink path="upsert">`UPSERT`</InternalLink>/<InternalLink path="update">`UPDATE`</InternalLink>/<InternalLink path="delete">`DELETE`</InternalLink> statements translate to key-value operations with minimal overhead (single digit percent slowdowns). For example, the following `UPSERT` to add or replace a row in the table would translate into a single key-value Put operation:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> UPSERT INTO kv VALUES (1, b'hello')
```

This SQL table approach also offers you a well-defined query language, a known transaction model, and the flexibility to add more columns to the table if the need arises.

## Does CockroachDB support full-text search?

Yes. For more information, see <InternalLink path="full-text-search">Full-Text Search</InternalLink>.

Depending on your use case, you may prefer to use <InternalLink path="trigram-indexes">trigram indexes</InternalLink> to do fuzzy string matching and pattern matching. For more information about use cases for trigram indexes that could make having full-text search unnecessary, see the 2022 blog post [Use cases for trigram indexes (when not to use Full Text Search)](https://www.cockroachlabs.com/blog/use-cases-trigram-indexes/).

## See also

* <InternalLink path="frequently-asked-questions">Product FAQs</InternalLink>
* <InternalLink path="operational-faqs">Operational FAQS</InternalLink>