> ## 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.
# UUID
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
{children}
;
};
The `UUID` (Universally Unique Identifier) data type stores a 128-bit value that is [unique across both space and time](https://www.ietf.org/rfc/rfc4122.txt).
To auto-generate unique row identifiers, use `UUID` with the `gen_random_uuid()` function as the default value. See the [example](#create-a-table-with-auto-generated-unique-row-ids) below for more details.
## Syntax
You can express `UUID` values using the following formats:
| Format | Description |
| -------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Standard [RFC4122](http://www.ietf.org/rfc/rfc4122.txt) format | Hyphen-separated groups of 8, 4, 4, 4, and 12 hexadecimal digits.
Example: `acde070d-8c4c-4f0d-9d8a-162843c10333` |
| `BYTES` | `UUID` value specified as a `BYTES` value.
Example: `b'kafef00ddeadbeed'` |
| Uniform Resource Name | A [Uniform Resource Name (URN)](https://www.ietf.org/rfc/rfc2141.txt) specified as "urn:uuid:" followed by the [RFC4122](http://www.ietf.org/rfc/rfc4122.txt) format.
Example: `urn:uuid:63616665-6630-3064-6465-616462656564` |
| Alternate PostgreSQL-supported formats | All [alternate `UUID` formats supported by PostgreSQL](https://www.postgresql.org/docs/current/datatype-uuid.html), including the [RFC4122](http://www.ietf.org/rfc/rfc4122.txt) format surrounded by braces, any supported format with upper-case digits, any supported format with some or all hyphens omitted, and any supported format with hyphens after any group of four digits.
Examples: {'{acde070d-8c4c-4f0d-9d8a-162843c10333}'}, `ACDE070D-8C4C-4f0D-9d8A-162843c10333`, `acde070d8c4c4f0d9d8a162843c10333`, `acde-070d-8c4c-4f0d-9d8a-1628-43c1-0333` |
CockroachDB displays all `UUID` values in the standard [RFC4122](http://www.ietf.org/rfc/rfc4122.txt) format.
## Size
A `UUID` value is 128 bits in width, but the total storage size is likely to be larger due to CockroachDB metadata.
## Examples
### Create a table with manually-entered `UUID` values
#### Create a table with `UUID` in standard [RFC4122](http://www.ietf.org/rfc/rfc4122.txt)-specified format
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE TABLE v (token uuid);
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> INSERT INTO v VALUES ('63616665-6630-3064-6465-616462656562');
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM v;
```
```
token
----------------------------------------
63616665-6630-3064-6465-616462656562
(1 row)
```
#### Create a table with `UUID` in `BYTE` format
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> INSERT INTO v VALUES (b'kafef00ddeadbeed');
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM v;
```
```
token
----------------------------------------
63616665-6630-3064-6465-616462656562
6b616665-6630-3064-6465-616462656564
(2 rows)
```
#### Create a table with `UUID` used as URN
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> INSERT INTO v VALUES ('urn:uuid:63616665-6630-3064-6465-616462656564');
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM v;
```
```
token
----------------------------------------
63616665-6630-3064-6465-616462656562
6b616665-6630-3064-6465-616462656564
63616665-6630-3064-6465-616462656564
(3 rows)
```
#### Express UUIDs in alternate formats
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> INSERT INTO v VALUES ('{acde070d-8c4c-4f0d-9d8a-162843c10333}'), ('ACDE070D-8C4C-4f0D-9d8A-162843c10333'), ('acde070d8c4c4f0d9d8a162843c10333'), ('acde-070d-8c4c-4f0d-9d8a-1628-43c1-0333');
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM v;
```
```
token
----------------------------------------
63616665-6630-3064-6465-616462656562
6b616665-6630-3064-6465-616462656564
63616665-6630-3064-6465-616462656564
acde070d-8c4c-4f0d-9d8a-162843c10333
acde070d-8c4c-4f0d-9d8a-162843c10333
acde070d-8c4c-4f0d-9d8a-162843c10333
acde070d-8c4c-4f0d-9d8a-162843c10333
(7 rows)
```
### Create a table with auto-generated unique row IDs
To auto-generate unique row identifiers, you can use the `gen_random_uuid()`, `uuid_v4()`, or `unique_rowid()` functions.
To use the `UUID` column with the `gen_random_uuid()` function as the default value:
```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)
```
Alternatively, you can use the `BYTES` 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 default size, 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.
If it is important for generated IDs to be stored in the same key-value range, you can use an integer type with the `unique_rowid()` function as the default value, either explicitly or via the `SERIAL` pseudo-type:
```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.
To understand the differences between the `UUID` and `unique_rowid()` options, see the SQL FAQs. For further background on UUIDs, see [What is a UUID, and Why Should You Care?](https://www.cockroachlabs.com/blog/what-is-a-uuid/).
## Supported casting and conversion
`UUID` values can be cast to the following data type:
| Type | Details |
| ------- | ------------------------------------------------------------------------------------------------------------------------ |
| `BYTES` | Requires supported `BYTES` string format, e.g., `b'\141\061\142\062\143\063'`. |
## See also
* Data Types
* [What is a UUID, and Why Should You Care?](https://www.cockroachlabs.com/blog/what-is-a-uuid/)