UUID

The UUID (Universally Unique Identifier) implements the UUIDv4 format from RFC 4122. It stores a 128-bit value that is “unique across both space and time, with respect to the space of all UUIDs” as specified by the RFC.

To auto-generate UUIDs:

• Use the gen_random_uuid() function as the default value of the UUID data type.
• Use the uuid_v4() function as the default value of the data type.

For examples, refer to Create a table with auto-generated unique row IDs.

For performance reasons, if you are going to use UUIDs, Cockroach Labs strongly recommends using UUIDv4 as defined by RFC 4122. This is the format generated by the . Other types of UUID are largely untested with CockroachDB and will require performance testing to avoid hotspots.

Syntax

You can express UUID values using the following formats:

Format	Description
Standard RFC4122 (UUIDv4) 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 value. Example: b'kafef00ddeadbeed'
Uniform Resource Name	A Uniform Resource Name (URN) specified as “urn:uuid:” followed by the RFC4122 format. Example: urn:uuid:63616665-6630-3064-6465-616462656564
Alternate PostgreSQL-supported formats	All alternate UUID formats supported by PostgreSQL, including the RFC4122 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, acde070d8c4c4f0d9d8a162843c10333, acde-070d-8c4c-4f0d-9d8a-1628-43c1-0333

CockroachDB displays all UUID values in the standard RFC4122 format, and implements the UUIDv4 (random) version from the RFC.

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

For performance reasons, if you are going to use UUIDs, Cockroach Labs strongly recommends using UUIDv4 as defined by RFC 4122. This is the format generated by the . Other types of UUID are largely untested with CockroachDB and will require performance testing to avoid hotspots.

Create a table with UUID in standard RFC4122-specified format

> CREATE TABLE v (token uuid);

> INSERT INTO v VALUES ('63616665-6630-3064-6465-616462656562');

> SELECT * FROM v;

                 token
----------------------------------------
  63616665-6630-3064-6465-616462656562
(1 row)

Create a table with UUID in BYTE format

> INSERT INTO v VALUES (b'kafef00ddeadbeed');

> SELECT * FROM v;

                 token
----------------------------------------
  63616665-6630-3064-6465-616462656562
  6b616665-6630-3064-6465-616462656564
(2 rows)

Create a table with UUID used as URN

> INSERT INTO v VALUES ('urn:uuid:63616665-6630-3064-6465-616462656564');

> 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

> INSERT INTO v VALUES ('{acde070d-8c4c-4f0d-9d8a-162843c10333}'), ('ACDE070D-8C4C-4f0D-9d8A-162843c10333'), ('acde070d8c4c4f0d9d8a162843c10333'), ('acde-070d-8c4c-4f0d-9d8a-1628-43c1-0333');

> 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 following :

• Use gen_random_uuid(): Generates a UUIDv4 with UUID data type.
• Use uuid_v4(): Generates a UUIDv4 with BYTES data type.
• 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. This is the format generated by the . Other types of UUID are largely untested with CockroachDB and will require performance testing to avoid hotspots.

Use gen_random_uuid()

To use the column with the gen_random_uuid() as the :

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)
);

INSERT INTO users (name, city) VALUES ('Petee', 'new york'), ('Eric', 'seattle'), ('Dan', 'seattle');

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)

Use uuid_v4()

Alternatively, you can use the column with the uuid_v4() function as the default value:

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)
);

INSERT INTO users2 (name, city) VALUES ('Anna', 'new york'), ('Jonah', 'seattle'), ('Terry', 'chicago');

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 , 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 with the unique_rowid() as the default value, either explicitly or via the :

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)
);

INSERT INTO users3 (name, city) VALUES ('Blake', 'chicago'), ('Hannah', 'seattle'), ('Bobby', 'seattle');

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 . For further background on UUIDs, see What is a UUID, and Why Should You Care?.

Supported casting and conversion

UUID values can be to the following data type:

Type	Details
BYTES	Requires supported string format, e.g., b'\141\061\142\062\143\063'.

See also

• What is a UUID, and Why Should You Care?