Pure SQL functions to use UUIDs v7 in PostgreSQL.
The functions are packaged as an extension ("uuidv7-sql") for convenience, but they may also be created individually by sourcing all or parts of the creation scripts.
The Makefile uses the PGXS infrastructure
To make the extension available to a PostgreSQL server:
$ [sudo] make install
The same, but targeting a specific PostgreSQL installation:
$ [sudo] make PG_CONFIG=/path/to/pg_config install
To activate the extension in the target databases:
CREATE EXTENSION "uuidv7-sql";
Generate a UUID v7 value using the current time with millisecond precision
and 74 bits of randomness. With PostgreSQL version 18 or newer,
a built-in function with the same name exists in pg_catalog,
coming first in the search path. You may prefix uuidv7 with the schema
(for instance public.uuidv7()) to specifically use the version of
this extension, or just use the built-in function instead.
Generate a UUID v7 value using the given time with millisecond precision and 74 bits of randomness.
Generate a UUID v7 value using the current time with sub-millisecond precision (up to 0.25 µs), with 62 bits of randomness.
Generate a UUID v7 value using the given time with sub-millisecond precision (up to 0.25 µs), with 62 bits of randomness.
Extract the timestamp with millisecond precision from the given UUID v7 value.
Generate a non-random uuidv7 with the given timestamp (first 48 bits) and all random bits to 0. As the smallest possible uuidv7 for that timestamp, it may be used as a boundary for tables partitioned by ranges of UUID.
Transform a UUID-v7 value into an equivalent UUID-v4 or UUID-v8 (passing 4 or 8 in uuid_ver) that does not leak the timestamp. The bit fields unix_ts_ms + rand_a + 4 more bits (total: 64 bits) from the UUID-v7 value are encrypted with an XTEA cipher. The 16-byte crypt_key parameter is the encryption key.
Decrypt a UUID (either v4 or v8) produced by uuidv7_encrypt() with the same crypt_key.