Decode a UUID and Identify Its Version

UUIDs look random… but they aren’t.

Every UUID encodes its version directly in the bits, and you can extract it easily in YugabyteDB YSQL (just like PostgreSQL), with no extensions required.

Let’s see how.

🧠 Where the UUID Version Lives

A canonical UUID looks like this:

				
					xxxxxxxx-xxxx-Mxxx-Nxxx-xxxxxxxxxxxx

M → UUID version
N → UUID variant

That M value is always the 15th character in the UUID string.

🧩 Decode the UUID Version (One-liner)

				
					SELECT substring(id::text FROM 15 FOR 1)::int AS uuid_version
FROM my_table;

Simple, fast, and works everywhere YSQL runs.

🧰 Create a Reusable Helper Function

For cleaner queries (and nicer demos):

				
					CREATE OR REPLACE FUNCTION uuid_version(u uuid)
RETURNS int
LANGUAGE sql
IMMUTABLE
AS $$
  SELECT substring(u::text FROM 15 FOR 1)::int;
$$;

🔬 Example: All Common UUID Versions

				
					SELECT
  id,
  uuid_version(id) AS version
FROM (VALUES
  ('6ba7b810-9dad-11d1-80b4-00c04fd430c8'::uuid), -- v1
  ('00000000-0000-2000-8000-000000000000'::uuid), -- v2
  ('f47ac10b-58cc-3372-a567-0e02b2c3d479'::uuid), -- v3
  ('550e8400-e29b-41d4-a716-446655440000'::uuid), -- v4
  ('987fbc97-4bed-5078-9f07-9141ba07c9f3'::uuid), -- v5
  ('018f3a9c-7c91-7b9f-b8c6-8d45a4cfe4ad'::uuid)  -- v7
) t(id);

				
					yugabyte=# SELECT
yugabyte-#   id,
yugabyte-#   uuid_version(id) AS version
yugabyte-# FROM (VALUES
yugabyte(#   ('6ba7b810-9dad-11d1-80b4-00c04fd430c8'::uuid), -- v1
yugabyte(#   ('00000000-0000-2000-8000-000000000000'::uuid), -- v2
yugabyte(#   ('f47ac10b-58cc-3372-a567-0e02b2c3d479'::uuid), -- v3
yugabyte(#   ('550e8400-e29b-41d4-a716-446655440000'::uuid), -- v4
yugabyte(#   ('987fbc97-4bed-5078-9f07-9141ba07c9f3'::uuid), -- v5
yugabyte(#   ('018f3a9c-7c91-7b9f-b8c6-8d45a4cfe4ad'::uuid)  -- v7
yugabyte(# ) t(id);
                  id                  | version
--------------------------------------+---------
 6ba7b810-9dad-11d1-80b4-00c04fd430c8 |       1
 00000000-0000-2000-8000-000000000000 |       2
 f47ac10b-58cc-3372-a567-0e02b2c3d479 |       3
 550e8400-e29b-41d4-a716-446655440000 |       4
 987fbc97-4bed-5078-9f07-9141ba07c9f3 |       5
 018f3a9c-7c91-7b9f-b8c6-8d45a4cfe4ad |       7
(6 rows)

🧬 Why So Many UUID Versions?

Version	Year	Why it exists
v1	1997	Time-based + node identifier (historically MAC). Sortable-ish, but can leak information.
v2	1997	DCE Security variant (rare in practice).
v3	2000	Namespace + name → deterministic UUID (MD5).
v4	2000	Random UUID (most common; great for uniqueness, not great for index locality).
v5	2000	Namespace + name → deterministic UUID (SHA-1).
v7	2022	Time-ordered + random (modern favorite for databases; improves index locality vs v4).

Notes:
• UUID v1 and v2 originate from the original DCE UUID specification (1997).
• UUID v3, v4, and v5 were standardized in RFC 4122 (2000).
• UUID v7 was introduced in RFC 9562 (2022) and is especially attractive for distributed SQL databases like YugabyteDB.

👉 See also: Generate UUIDv7 in YSQL

🎯 Takeaway

● PostgreSQL & YugabyteDB don’t expose a built-in uuid_version() function
● But extracting it is trivial, safe, and fast
● UUIDv7 is worth a serious look for write-heavy, globally distributed workloads

Have Fun!