Skip to content

Instantly share code, notes, and snippets.

@bjeanes
Last active June 20, 2024 17:24
Show Gist options
  • Save bjeanes/c1d751395effec72ac6631211cb84718 to your computer and use it in GitHub Desktop.
Save bjeanes/c1d751395effec72ac6631211cb84718 to your computer and use it in GitHub Desktop.
Postgres UUIDv6 (non-standard)

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

Licence

Licenced under ISC.

Copyright 2020 Bo Jeanes (@bjeanes)

Permission to use, copy, modify, and/or distribute this software for any purpose with or without fee is hereby granted, provided that the above copyright notice and this permission notice appear in all copies.

THE SOFTWARE IS PROVIDED "AS IS" AND THE AUTHOR DISCLAIMS ALL WARRANTIES WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR ANY SPECIAL, DIRECT, INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE.
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)
@larsmjoh
Copy link

Hi,

May I reuse this code in a commercial setting under, say the ISC license attributing you as original author?

/Lars

@bjeanes
Copy link
Author

bjeanes commented Jun 1, 2021

@larsmjoh ISC suits me just fine. Go ahead!

I'll edit the gist with a license for future readers too.

I appreciate you asking!

@bjeanes
Copy link
Author

bjeanes commented Jun 1, 2021

I have started drifting towards the MPL license too. ISC is fine for this but if you do improve this, this is my request that those improvements are shared again here! <3

@larsmjoh
Copy link

larsmjoh commented Jun 2, 2021

@larsmjoh ISC suits me just fine. Go ahead!

I'll edit the gist with a license for future readers too.

I appreciate you asking!

Nice, thanks!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment