Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Postgres UUIDv6 (non-standard)
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
-- My port of https://github.com/kurttheviking/uuid-with-v6-js
-- A hacky translation of:
-- - http://gh.peabody.io/uuidv6/
-- - https://tools.ietf.org/html/draft-peabody-dispatch-new-uuid-format
CREATE OR REPLACE FUNCTION uuid_v1_to_v6(v1 uuid)
RETURNS uuid AS $$
DECLARE
v6 text;
BEGIN
SELECT substring(v1::text from 16 for 3) ||
substring(v1::text from 10 for 4) ||
substring(v1::text from 1 for 5) ||
'6' || substring(v1::text from 6 for 3) ||
substring(v1::text from 20)
INTO v6;
RETURN v6::uuid;
END; $$
LANGUAGE PLPGSQL;
CREATE OR REPLACE FUNCTION uuid_generate_v6mc()
RETURNS uuid AS $$
BEGIN
RETURN uuid_v1_to_v6(uuid_generate_v1mc());
END; $$
LANGUAGE PLPGSQL;
CREATE OR REPLACE FUNCTION uuid_generate_v6()
RETURNS uuid AS $$
BEGIN
RETURN uuid_v1_to_v6(uuid_generate_v1());
END; $$
LANGUAGE PLPGSQL;
# select uuid_generate_v6mc() from generate_series(1,20);
          uuid_generate_v6mc
--------------------------------------
 1eaa7858-863b-689c-9fb4-87df3813ba36
 1eaa7858-863b-69c8-9fb4-bfc6e6e3a1f2
 1eaa7858-863b-6a54-9fb4-9f139bb05d7b
 1eaa7858-863b-6ad6-9fb4-87f6e9bb691f
 1eaa7858-863b-6b4e-9fb4-f72b808b9a8f
 1eaa7858-863b-6bd0-9fb4-077dc69a4bf8
 1eaa7858-863b-6c48-9fb4-c70ecaccee2f
 1eaa7858-863b-6cca-9fb4-fbf95e0edadf
 1eaa7858-863b-6d42-9fb4-cbd804a00457
 1eaa7858-863b-6e32-9fb4-9baa9f476601
 1eaa7858-863b-6ee6-9fb4-e3f33d0d9577
 1eaa7858-863b-6f72-9fb4-2779ce4270e8
 1eaa7858-863b-6ff4-9fb4-ef2cea331f95
 1eaa7858-863c-606c-9fb4-fbcdb18ae615
 1eaa7858-863c-60ee-9fb4-33c7b54c58b6
 1eaa7858-863c-6166-9fb4-17d009923e41
 1eaa7858-863c-61e8-9fb4-27714feb33fb
 1eaa7858-863c-6260-9fb4-738ce6f88eb2
 1eaa7858-863c-62e2-9fb4-9b7794839b18
 1eaa7858-863c-635a-9fb4-fbf96ab41c09
(20 rows)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.