UUID

On this page Carat arrow pointing down

New in v1.1: The UUID (Universally Unique Identifier) data type stores a 128-bit value that is unique across both space and time.

Tip:
To auto-generate unique row IDs, we recommend using UUID with the gen_random_uuid() function as the default value. See the example below for more details.

Syntax

A UUID value can be expressed using the following formats:

Format Description
Standard RCF4122-specified format Hyphen-separated groups of 8, 4, 4, 4, 12 hexadecimal digits.

Example: acde070d-8c4c-4f0d-9d8a-162843c10333
With braces The standard RCF4122-specified format with braces.

Example: {acde070d-8c4c-4f0d-9d8a-162843c10333}
As BYTES UUID value specified as bytes.

Example: b'kafef00ddeadbeed'
UUID used as a URN UUID can be used as a Uniform Resource Name (URN). In that case, the format is specified as "urn:uuid:" followed by standard RCF4122-specified format.

Example: urn:uuid:63616665-6630-3064-6465-616462656564

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 RCF4122-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 standard RCF4122-specified format with braces

> INSERT INTO v VALUES ('{63616665-6630-3064-6465-616462656563}');

> SELECT * FROM v;
+--------------------------------------+
|                token                 |
+--------------------------------------+
| 63616665-6630-3064-6465-616462656562 |
| 63616665-6630-3064-6465-616462656563 |
+--------------------------------------+
(2 rows)

Create a table with UUID in byte format

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

> SELECT * FROM v;
+--------------------------------------+
|                token                 |
+--------------------------------------+
| 63616665-6630-3064-6465-616462656562 |
| 63616665-6630-3064-6465-616462656563 |
| 6b616665-6630-3064-6465-616462656564 |
+--------------------------------------+
(3 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 |
| 63616665-6630-3064-6465-616462656563 |
| 6b616665-6630-3064-6465-616462656564 |
| 63616665-6630-3064-6465-616462656564 |
+--------------------------------------+
(4 rows)

Create a table with auto-generated unique row IDs

To auto-generate unique row IDs, use the UUID column with the gen_random_uuid() function as the default value:

icon/buttons/copy
> CREATE TABLE t1 (id UUID PRIMARY KEY DEFAULT gen_random_uuid(), name STRING);
icon/buttons/copy
> INSERT INTO t1 (name) VALUES ('a'), ('b'), ('c');
icon/buttons/copy
> 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:

icon/buttons/copy
> CREATE TABLE t2 (id BYTES PRIMARY KEY DEFAULT uuid_v4(), name STRING);
icon/buttons/copy
> INSERT INTO t2 (name) VALUES ('a'), ('b'), ('c');
icon/buttons/copy
> 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:

icon/buttons/copy
> CREATE TABLE t3 (id SERIAL PRIMARY KEY, name STRING);
icon/buttons/copy
> INSERT INTO t3 (name) VALUES ('a'), ('b'), ('c');
icon/buttons/copy
> 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.

Supported Casting & 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


Yes No
On this page

Yes No