Postgres UUIDv6 (non-standard)

README.md

Postgres UUIDv6 (non-standard) using randomised MAC address.

I.e. lexicographically sortable (and indexable) UUIDs.

Version bit set to 6.

Ported from https://github.com/kurttheviking/uuid-with-v6-js. which is an interpretation of

• http://gh.peabody.io/uuidv6/ /
• https://tools.ietf.org/html/draft-peabody-dispatch-new-uuid-format

AAA_uuid_generate_v6mc.sql

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;

example.md

# 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)