-
-
Save materro/e13864e94318e49d7313d62af9ca73d6 to your computer and use it in GitHub Desktop.
Function for generating time-ordered UUIDs (UUIDv6) on PostgreSQL 8+
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| /** | |
| * Returns a time-ordered UUID (UUIDv6). | |
| * | |
| * Tags: uuid guid uuid-generator guid-generator generator time order rfc4122 rfc-4122 postgres postgres8 | |
| */ | |
| CREATE OR REPLACE FUNCTION generate_uuid_v6() RETURNS VARCHAR(36) AS | |
| $$ | |
| DECLARE | |
| v_time TIMESTAMP WITH TIME ZONE; | |
| v_secs BIGINT; | |
| v_usec BIGINT; | |
| v_timestamp BIGINT; | |
| v_timestamp_hex VARCHAR; | |
| v_clkseq_and_nodeid BIGINT; | |
| v_clkseq_and_nodeid_hex VARCHAR; | |
| v_bytes BYTEA; | |
| c_epoch BIGINT := -12219292800; -- RFC-4122 epoch: '1582-10-15 00:00:00' | |
| c_variant BIT(64):= x'8000000000000000'; -- RFC-4122 variant: b'10xx...' | |
| v_uuid VARCHAR(36); | |
| BEGIN | |
| -- Get seconds and micros | |
| v_time := TIMEOFDAY()::TIMESTAMP WITH TIME ZONE; | |
| v_secs := EXTRACT(EPOCH FROM v_time)::BIGINT; | |
| v_usec := mod(EXTRACT(MICROSECONDS FROM v_time)::NUMERIC, 10^6::NUMERIC)::BIGINT; | |
| -- Generate timestamp hexadecimal (and set version 6) | |
| v_timestamp := (((v_secs - c_epoch) * 10^6) + v_usec)::BIGINT * 10; | |
| v_timestamp_hex := lpad(to_hex(v_timestamp), 16, '0'); | |
| v_timestamp_hex := substr(v_timestamp_hex, 2, 12) || '6' || substr(v_timestamp_hex, 14, 3); | |
| -- Generate clock sequence and node identifier hexadecimal (and set variant b'10xx') | |
| v_clkseq_and_nodeid := ((random()::numeric * 2^62::numeric)::BIGINT::bit(64) | c_variant)::BIGINT; | |
| v_clkseq_and_nodeid_hex := lpad(to_hex(v_clkseq_and_nodeid), 16, '0'); | |
| -- Concat timestemp, clock sequence and node identifier hexadecimal | |
| v_bytes := decode(v_timestamp_hex || v_clkseq_and_nodeid_hex, 'hex'); | |
| v_uuid := encode(v_bytes, 'hex'); | |
| RETURN substr(v_uuid, 1, 8) || | |
| '-' || substr(v_uuid, 9, 4) || | |
| '-' || substr(v_uuid, 13, 4) || | |
| '-' || substr(v_uuid, 17, 4) || | |
| '-' || substr(v_uuid, 21); | |
| END | |
| $$ | |
| LANGUAGE plpgsql; | |
| -- Example: | |
| -- SELECT generate_uuid_v6() AS uuid, TIMEOFDAY()::TIMESTAMP - NOW() AS time_taken; | |
| -- Example output: | |
| -- uuid | time_taken | |
| -- --------------------------------------+----------------- | |
| -- 1edc33a9-d37b-6808-851b-5ecc0a36be2f | 00:00:00.000412 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment