How are UUIDs sorted / compared ?

From: PG Doc comments form <noreply(at)postgresql(dot)org>
To: pgsql-docs(at)lists(dot)postgresql(dot)org
Cc: lbruun(at)apache(dot)org
Subject: How are UUIDs sorted / compared ?
Date: 2021-03-15 07:26:45
Message-ID: 161579320575.10135.15674918680852884624@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-docs

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/13/datatype-uuid.html
Description:

I'm missing information about what really sets the UUID datatype apart?

For example I'm missing information as to how the UUID is sorted, i.e. how
two UUIDs are compared to one another. (This is crucial knowledge for a user
who tries to understand if the UUID datatype is a good choice, for example
as a PK.)

The reason why this is not obvious is that there are multiple ways to look
at it. Microsoft has written an excellent article on the topic, named "How
many ways are there to sort GUIDs? How much time do you have?" [1], which
explains it well. So which method does PostgreSQL use?

The other thing worth documenting is if PostgreSQL enforces some kind of
standard compliance on the UUID. As far as I can tell it doesn't, you can
put any 16 bytes into a UUID type and no validation will take place, right?.
If that's the case then perhaps worth documenting how the UUID type is
really different from bytea(16) - if such a thing existed.

Bottom line: from what I can tell, the UUID data type is really just an byte
array of length 16 to the backend with no intrinsic knowledge of what those
bytes represent. What makes the UUID type special is that

- Presentation: When the UUID datatype is presented it is done so in
traditional RFC 4122 format, i.e. 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'
- Input: The datatype accepts input in RFC 4122 format

...but apart from this ? Just a byte array, right?

/Lars

[1] https://devblogs.microsoft.com/oldnewthing/20190426-00/?p=102450

Browse pgsql-docs by date

  From Date Subject
Next Message Jürgen Purtz 2021-03-15 08:06:51 Re: Change JOIN tutorial to focus more on explicit joins
Previous Message Pavel Stehule 2021-03-15 04:28:44 Re: Change JOIN tutorial to focus more on explicit joins