Functions for generating UUIDv6 and UUIDv7 on PostgreSQL

UUIDv6.sql

/*
 * MIT License
 *
 * Copyright (c) 2023-2024 Fabio Lima
 * 
 *  Permission is hereby granted, free of charge, to any person obtaining a copy
 *  of this software and associated documentation files (the "Software"), to deal
 *  in the Software without restriction, including without limitation the rights
 *  to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
 *  copies of the Software, and to permit persons to whom the Software is
 *  furnished to do so, subject to the following conditions:
 * 
 *  The above copyright notice and this permission notice shall be included in
 *  all copies or substantial portions of the Software.
 * 
 *  THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
 *  IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
 *  FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
 *  AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
 *  LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
 *  OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
 *  THE SOFTWARE.
 */
 
/**
 * Returns a time-ordered UUID with Gregorian Epoch (UUIDv6).
 *
 * Referencie: https://www.rfc-editor.org/rfc/rfc9562.html
 *
 * MIT License.
 *
 */
create or replace function uuidv6(p_timestamp timestamp with time zone default clock_timestamp()) returns uuid as $$
declare

	v_time double precision := null;

	v_gregorian_t bigint := null;
	v_clock_sequence_and_node bigint := null;

	v_gregorian_t_hex_a varchar := null;
	v_gregorian_t_hex_b varchar := null;
	v_clock_sequence_and_node_hex varchar := null;

	c_epoch double precision := 12219292800; -- RFC-9562 epoch: 1582-10-15
	c_100ns_factor double precision := 10^7; -- RFC-9562 precision: 100 ns

	c_version bigint := x'0000000000006000'::bigint; -- RFC-9562 version: b'0110...'
	c_variant bigint := x'8000000000000000'::bigint; -- RFC-9562 variant: b'10xx...'

begin

	v_time := extract(epoch from p_timestamp);

	v_gregorian_t := trunc((v_time + c_epoch) * c_100ns_factor);
	-- v_clock_sequence_and_node := secure_random_bigint(); -- use when pgcrypto extension is installed
	v_clock_sequence_and_node := trunc(random() * 2^30)::bigint << 32 | trunc(random() * 2^32)::bigint;

	v_gregorian_t_hex_a := lpad(to_hex((v_gregorian_t >> 12)), 12, '0');
	v_gregorian_t_hex_b := lpad(to_hex((v_gregorian_t & 4095) | c_version), 4, '0');
	v_clock_sequence_and_node_hex := lpad(to_hex(v_clock_sequence_and_node | c_variant), 16, '0');

	return (v_gregorian_t_hex_a || v_gregorian_t_hex_b  || v_clock_sequence_and_node_hex)::uuid;

end $$ language plpgsql;

-------------------------------------------------------------------
-- EXAMPLE:
-------------------------------------------------------------------
-- 
-- select uuid6() uuid, clock_timestamp()-statement_timestamp() time_taken;
--
-- |uuid                                  |time_taken        |
-- |--------------------------------------|------------------|
-- |1eeca632-cf2a-65e0-85f3-151064c2409d  |00:00:00.000108   |
-- 

-------------------------------------------------------------------
-- EXAMPLE: generate a list
-------------------------------------------------------------------
-- 
-- with x as (select clock_timestamp() as t from generate_series(1, 1000))
-- select uuid6(x.t) uuid, x.t::text ts from x;
-- 
-- |uuid                                |ts                           |
-- |------------------------------------|-----------------------------|
-- |1eeca634-f783-63f0-9988-48906d79f782|2024-02-13 08:30:37.891480-03|
-- |1eeca634-f783-6c24-97af-605238f4c3d0|2024-02-13 08:30:37.891691-03|
-- |1eeca634-f783-6e7c-9c2e-624f24b87738|2024-02-13 08:30:37.891754-03|
-- |1eeca634-f784-6070-a67b-4fc6659143e7|2024-02-13 08:30:37.891800-03|
-- |1eeca634-f784-6200-befd-0e20be5b0087|2024-02-13 08:30:37.891842-03|
-- |1eeca634-f784-6390-8f79-d4dacec1c3e0|2024-02-13 08:30:37.891881-03|
-- |1eeca634-f784-6520-8ee7-96091b017d4c|2024-02-13 08:30:37.891920-03|
-- |1eeca634-f784-66b0-a63e-c285d8a63e21|2024-02-13 08:30:37.891958-03|
-- |1eeca634-f784-6840-8c00-38659c4bf807|2024-02-13 08:30:37.891997-03|
-- |1eeca634-f784-69d0-b775-4bbfd45eb99e|2024-02-13 08:30:37.892036-03|
-- 

-------------------------------------------------------------------
-- FOR TEST: the expected result is an empty result set
-------------------------------------------------------------------
-- 
-- with t as (select uuid6() as id from generate_series(1, 1000))
-- select * from t where (id is null or id::text !~ '^[a-f0-9]{8}-[a-f0-9]{4}-6[a-f0-9]{3}-[89ab][a-f0-9]{3}-[a-f0-9]{12}$');
--

UUIDv7.sql

/*
 * MIT License
 *
 * Copyright (c) 2023-2024 Fabio Lima
 * 
 *  Permission is hereby granted, free of charge, to any person obtaining a copy
 *  of this software and associated documentation files (the "Software"), to deal
 *  in the Software without restriction, including without limitation the rights
 *  to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
 *  copies of the Software, and to permit persons to whom the Software is
 *  furnished to do so, subject to the following conditions:
 * 
 *  The above copyright notice and this permission notice shall be included in
 *  all copies or substantial portions of the Software.
 * 
 *  THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
 *  IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
 *  FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
 *  AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
 *  LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
 *  OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
 *  THE SOFTWARE.
 */
 
/**
 * Returns a time-ordered UUID with Unix Epoch (UUIDv7).
 * 
 * Referencie: https://www.rfc-editor.org/rfc/rfc9562.html
 *
 * MIT License.
 *
 */
create or replace function uuidv7(p_timestamp timestamp with time zone default clock_timestamp()) returns uuid as $$
declare

	v_time double precision := null;

	v_unix_t bigint := null;
	v_rand_a bigint := null;
	v_rand_b bigint := null;

	v_unix_t_hex varchar := null;
	v_rand_a_hex varchar := null;
	v_rand_b_hex varchar := null;

	c_milli double precision := 10^3;  -- 1 000
	c_micro double precision := 10^6;  -- 1 000 000
	c_scale double precision := 4.096; -- 4.0 * (1024 / 1000)
	
	c_version bigint := x'0000000000007000'::bigint; -- RFC-9562 version: b'0111...'
	c_variant bigint := x'8000000000000000'::bigint; -- RFC-9562 variant: b'10xx...'

begin

	v_time := extract(epoch from p_timestamp);

	v_unix_t := trunc(v_time * c_milli);
	v_rand_a := trunc((v_time * c_micro - v_unix_t * c_milli) * c_scale);
	-- v_rand_b := secure_random_bigint(); -- use when pgcrypto extension is installed
	v_rand_b := trunc(random() * 2^30)::bigint << 32 | trunc(random() * 2^32)::bigint;

	v_unix_t_hex := lpad(to_hex(v_unix_t), 12, '0');
	v_rand_a_hex := lpad(to_hex((v_rand_a | c_version)::bigint), 4, '0');
	v_rand_b_hex := lpad(to_hex((v_rand_b | c_variant)::bigint), 16, '0');

	return (v_unix_t_hex || v_rand_a_hex || v_rand_b_hex)::uuid;
	
end $$ language plpgsql;

-------------------------------------------------------------------
-- EXAMPLE:
-------------------------------------------------------------------
-- 
-- select uuid7() uuid, clock_timestamp()-statement_timestamp() time_taken;
--
-- |uuid                                  |time_taken        |
-- |--------------------------------------|------------------|
-- |018da240-e0db-72e1-86f5-345c2c240387  |00:00:00.000222   |
-- 

-------------------------------------------------------------------
-- EXAMPLE: generate a list
-------------------------------------------------------------------
-- 
-- with x as (select clock_timestamp() as t from generate_series(1, 1000))
-- select uuid7(x.t) uuid, x.t::text ts from x;
-- 
-- |uuid                                |ts                           |
-- |------------------------------------|-----------------------------|
-- |018da235-6271-70cd-a937-0bb7d22b801e|2024-02-13 08:23:44.113054-03|
-- |018da235-6271-7214-9188-1d3191883b5d|2024-02-13 08:23:44.113126-03|
-- |018da235-6271-723d-bebe-87f66085fad7|2024-02-13 08:23:44.113143-03|
-- |018da235-6271-728f-86ba-6e277d10c0a3|2024-02-13 08:23:44.113156-03|
-- |018da235-6271-72b8-9887-f31e4ca48020|2024-02-13 08:23:44.113168-03|
-- |018da235-6271-72e1-bbeb-8b686d0d4281|2024-02-13 08:23:44.113179-03|
-- |018da235-6271-730a-96a2-73275626f72a|2024-02-13 08:23:44.113190-03|
-- |018da235-6271-7333-8a5c-9d1ab89dc489|2024-02-13 08:23:44.113201-03|
-- |018da235-6271-735c-ba64-a42b55ad7d5c|2024-02-13 08:23:44.113212-03|
-- |018da235-6271-7385-a0fb-c65f5be24073|2024-02-13 08:23:44.113223-03|
--

-------------------------------------------------------------------
-- BENCHMARK: generate 1 million UUIDs and print the time taken
-------------------------------------------------------------------

-- DO $$
-- DECLARE
-- v_start double precision;
-- v_end   double precision;
-- v_count bigint;   
-- BEGIN
-- 	v_start := extract(epoch from clock_timestamp());
-- 	with x as (select uuid7() id from generate_series(1, 1000000))
-- 	select count(id) into v_count from x;
-- 	v_end := extract(epoch from clock_timestamp());
-- 	raise notice 'Time taken: %s', v_end - v_start;
-- END
-- $$ language plpgsql;

-- SYSTEM OUTPUT
-- Time taken: 2.848252058029175s [with PostgreSQL's random() ]
-- Time taken: 5.202008008956909s [with secure_random_bigint()]

-------------------------------------------------------------------
-- FOR TEST: the expected result is an empty result set
-------------------------------------------------------------------
-- 
-- with t as (select uuid7() as id from generate_series(1, 1000))
-- select * from t where (id is null or id::text !~ '^[a-f0-9]{8}-[a-f0-9]{4}-7[a-f0-9]{3}-[89ab][a-f0-9]{3}-[a-f0-9]{12}$');
--

x_secure_random_bigint.sql

-- install the extension
CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE OR REPLACE FUNCTION secure_random_bigint() RETURNS bigint AS $$
DECLARE
    v_bytes bytea;
    v_value bigint := 0;
    v_length integer := 8;
    i integer := 0;
BEGIN
	v_bytes := gen_random_bytes(v_length);
	FOR i IN 0..v_length-1 LOOP
		v_value := (v_value << 8) | get_byte(v_bytes, i);
	END LOOP;
    RETURN v_value::bigint;
END;
$$ LANGUAGE plpgsql;

Refactored.

When will v6 become stable, is there any news about it?

I don't know. I hope draft 14 is the last and the new RFC is finally completed.

There are no updates to the email archive:
https://mailarchive.ietf.org/arch/browse/uuidrev/

I had to cast 2^12 as numeric to get this to work in PostgreSQL version 16.2

DIV(v_gregorian_t, 2^12::NUMERIC)
MOD(v_gregorian_t, 2^12::NUMERIC)

Dude, fantastic job! \m/

An anonymous said and deleted later:

Getting ERROR: invalid input syntax for type uuid: "018d3f3b-4390-7375-9acd-7611c9a7c" error on some uuids generated by these functions.

The last 3 hex digits are missing in 018d3f3b-4390-7375-9acd-7611c9a7c???.

For this error to occur inside the function, the following condition must be true for some return of random(), but I didn't find any value that would cause this.

select length(to_hex(((random()::numeric * 2^62::numeric)::bigint::bit(64) | x'8000000000000000')::bigint)) < 16;

I think rand_b can be split into two 32-bit random(), just in case of another floating point issue.

Instead, I just removed the decode() and encode() operations because they are no longer needed.

Diffs:

-	v_output_bytes := decode(v_gregorian_t_hex_a || v_gregorian_t_hex_b  || v_clock_sequence_and_node_hex, 'hex');
- 	return encode(v_output_bytes, 'hex')::uuid;
+ 	return (v_gregorian_t_hex_a || v_gregorian_t_hex_b  || v_clock_sequence_and_node_hex)::uuid;

-	v_output_bytes := decode(v_unix_t_hex || v_rand_a_hex || v_rand_b_hex, 'hex');
-	return encode(v_output_bytes, 'hex')::uuid;
+	return (v_unix_t_hex || v_rand_a_hex || v_rand_b_hex)::uuid;

Thanks any way, anonymous!

v_rand_b := random()::numeric * 2^62::numeric;

This line wants to produce 62 random bits, but a double between 0 and 1 only has 52 bits in the mantissa. Casting it to numeric doesn't give you more bits of entropy.

You need to do something like:

-- Generate a random 64-bit integer
create or replace function random_bigint() returns bigint as $$
declare
    hi bigint;
    lo bigint;
begin
    -- generate a number from 0 to 4294967295 inclusive
    hi := (random() * 0x100000000)::bigint;
    lo := (random() * 0x100000000)::bigint;
    return (hi << 32) | lo;
end;
$$ language plpgsql;

You are right @eloff. I missed the mantissa detail.

Replaced all numeric data types with bigint or double precision. I was using numeric to resist IEEE 754 floating point arithmetic, but this resistance is futile.

Here are the diff lines related to the variable v_rand_b:

- 	v_rand_b := random()::numeric * 2^62::numeric;
+ 	v_rand_b := trunc(random() * 2^30)::bigint << 32 | trunc(random() * 2^32)::bigint;

I think the functions are simpler now (and maybe faster).

Thank you for letting me know! 👍

hey,
ive got the biggest noob question and i am sorry to pollute this gist with it. but ...

i've created the function. ie just executed the `uuid7' sql above.
then i added the function to my prisma.schema like this:

model User {
  id          String  @id @default(dbgenerated("uuid7()"))

but trying to deploy that i get:

ERROR: function uuid7() does not exist

i get the sense that i have to turn this into an extension?!
whats wrong with my approach?

ill show myself back into my skill-issue corner.

I'm the biggest noob in prisma. Sorry.

But it looks like a search_path problem. Check these issues:

• prisma/prisma#7709
• prisma/prisma#14662 (comment)

EDIT:

• prisma-extension-uuid7: https://snyk.io/advisor/npm-package/prisma-extension-uuid7#package-footer

Performance validation for UUIDv7 implemented using SQL in PostgreSQL v16 (Monday, July 8, 2024)

https://www.dbaglobe.com/2024/07/performance-validation-for-uuidv7.html

Choosing Primary Key Type in PostgreSQL (June 23, 2022)

https://shekhargulati.com/2022/06/23/choosing-a-primary-key-type-in-postgres/

UUIDv7 is now in .Net 9! 🚀

https://learn.microsoft.com/en-us/dotnet/api/system.guid.createversion7?view=net-9.0

Meanwhile in Java... 🦗

https://bugs.openjdk.org/browse/JDK-8334015

I'm not a postgres or crypto expert, so there is probably a better way to do this, but could you use something like this to replace random() if pgcrypto is available to satisfy section 6.9's SHOULD? This should be 64 bits of randomness in a 0-1 range, though I just wrote it and looked at some anecdotal output, haven't ran it against a test suite.

CREATE OR REPLACE FUNCTION secure_random() RETURNS double precision AS $$
DECLARE
    -- Use 8 bytes (64 bits) for high precision
    v_bytes bytea;
    v_int bigint;
BEGIN
    v_bytes := gen_random_bytes(8);
    -- Convert bytes to bigint (using bitwise AND to ensure positive number)
    v_int := ((get_byte(v_bytes, 0)::bigint << 56) |
             (get_byte(v_bytes, 1)::bigint << 48) |
             (get_byte(v_bytes, 2)::bigint << 40) |
             (get_byte(v_bytes, 3)::bigint << 32) |
             (get_byte(v_bytes, 4)::bigint << 24) |
             (get_byte(v_bytes, 5)::bigint << 16) |
             (get_byte(v_bytes, 6)::bigint << 8) |
             get_byte(v_bytes, 7)::bigint) & x'7FFFFFFFFFFFFFFF'::bigint;
    -- Convert to [0, 1) range using max positive bigint value
    RETURN (v_int::numeric / (2^63)::numeric)::double precision;
END;
$$ LANGUAGE plpgsql;

Hi @jdconley !

I'm not an expert on any of them. I always learn valuable things from interactions here on github.

Your function is very useful! The only line we need to change is this one in the uuid7() function:

	v_unix_t := trunc(v_time * c_milli);
	v_rand_a := trunc((v_time * c_micro - v_unix_t * c_milli) * c_scale);
-	v_rand_b := trunc(random() * 2^30)::bigint << 32 | trunc(random() * 2^32)::bigint;
+	v_rand_b := trunc(secure_random() * 2^30)::bigint << 32 | trunc(secure_random() * 2^32)::bigint;

I was thinking of adding a few lines like in another GIST of mine, but your function is more generic and can be reused as well.

Also you're absolutely right about recommending a cryptographically secure generator for the random component of UUIDs. However, functions should I am of the opinion that these functions should be as simple as possible while still conforming to the standard, except for that specific point you mentioned.

Thanks for your suggestion!

Also you're absolutely right about recommending a cryptographically secure generator for the random component of UUIDs. However, functions should be as simple as possible while still conforming to the standard, except for that specific point you mentioned.

I imagine it is going to slow down the benchmarks, but, would be a good option for people that want a secure option (e.g. if these id's are publicly available) assuming my implementation isn't complete 💩

I just added secure_random_bigint(), which can be used when pgcrypto is available.

Benchmark generating 1 million UUIDv7:

• Using PostgreSQL's random() (twice): 2.84 s
• Using secure_random_bigint() (once): 5.20 s

Summary: using secure_random_bigint() took 83% longer compared to using random() twice in the uuid7() function.

---

Also created some random functions using bytes provided by pgcrypto. The secure_random_double() took more time because I had to learn how IEEE 754 deals with the mantissa's 52 bits. Today I learned.

@fabiolimace

It seems that two functions:

function uuid7() returns uuid
function uuid7(p_timestamp timestamp with time zone) returns uuid

could be simplified to one:

function uuid7(p_timestamp timestamp with time zone default clock_timestamp()) returns uuid

Please check it.

Why does the v6 function is called v7?

I would change CREATE EXTENSION pgcrypto; to CREATE EXTENSION IF NOT EXISTS pgcrypto;

Why does the v6 function is called v7?

oops!

I'll fix it.

Hi @lospejos and @liel-almog !

I appreciate your suggestions. Thank you!

Renamed uuid6() to uuidv6() and uuid7() to uuidv7() (with a "v") to make the transition to PostgreSQL 18+ smoother.