Skip to content

Instantly share code, notes, and snippets.

@szalansky
Forked from kjmph/A_UUID_v7_for_Postgres.sql
Created October 13, 2022 10:48
Show Gist options
  • Save szalansky/1934b483680ffe5b3c3807e24fe3c9c6 to your computer and use it in GitHub Desktop.
Save szalansky/1934b483680ffe5b3c3807e24fe3c9c6 to your computer and use it in GitHub Desktop.
Postgres PL/pgSQL function for UUID v7 and a bonus custom UUID v8 to support microsecond precision as well.
create or replace function uuid_generate_v7()
returns uuid
as $$
declare
unix_ts_ms bytea;
uuid_bytes bytea;
begin
unix_ts_ms = substring(int8send(floor(extract(epoch from clock_timestamp()) * 1000)::bigint) from 3);
-- use random v4 uuid as starting point (which has the same variant we need)
uuid_bytes = uuid_send(gen_random_uuid());
-- overlay timestamp
uuid_bytes = overlay(uuid_bytes placing unix_ts_ms from 1 for 6);
-- set version 7
uuid_bytes = set_byte(uuid_bytes, 6, (b'0111' || get_byte(uuid_bytes, 6)::bit(4))::bit(8)::int);
return encode(uuid_bytes, 'hex')::uuid;
end
$$
language plpgsql
volatile;
-- Generate a custom UUID v8 with microsecond precision
create or replace function uuid_generate_v8()
returns uuid
as $$
declare
unix_ts_ms bytea;
uuid_bytes bytea;
timestamp timestamptz;
microseconds int;
begin
timestamp = clock_timestamp();
unix_ts_ms = substring(int8send(floor(extract(epoch from timestamp) * 1000)::bigint) from 3);
microseconds = (cast(extract(microseconds from timestamp)::int - (floor(extract(milliseconds from timestamp))::int * 1000) as double precision) * 4.096)::int;
-- use random v4 uuid as starting point (which has the same variant we need)
uuid_bytes = uuid_send(gen_random_uuid());
-- overlay timestamp
uuid_bytes = overlay(uuid_bytes placing unix_ts_ms from 1 for 6);
-- set version 8
uuid_bytes = set_byte(uuid_bytes, 6, (b'1000' || (microseconds >> 8)::bit(4))::bit(8)::int);
uuid_bytes = set_byte(uuid_bytes, 7, microseconds::bit(8)::int);
return encode(uuid_bytes, 'hex')::uuid;
end
$$
language plpgsql
volatile;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment