How to sort in PostgreSQL via UUID?

With UIDs As (-- 0 1 2 3 4 5 6 7 8 9 ABCDEF
 Select ID ='F', UID = cast ('00000000-0000-0000-0000-000000000011' as uniqueidentifier)
 Union Select ID ='E', UID = cast (' 00000000-0000-0000-0000-000000001100' as uniqueidentifier)
 Union Select ID ='D', UID = cast ('00000000-0000-0000-0000-000000110000' as uniqueidentifier)
 Union Select ID ='C', UID = cast ('00000000-0000-0000-0000-000011000000' as uniqueidentifier)
 Union Select ID ='B', UID = cast ('00000000-0000-0000-0000-001100000000' as uniqueidentifier)
 Union S elect ID ='A', UID = cast ('00000000-0000-0000-0000-110000000000' as uniqueidentifier)
 Union Select ID = '9', UID = cast ('00000000-0000-0000-0011- 000000000000' as uniqueidentifier)
 Union Select ID = '8', UID = cast ('00000000-0000-0000-1100-000000000000' as uniqueidentifier)
 Union Select ID = '7', UID = cast ('00000000-0000-0011-0000-000000000000' as uniqueidentifier)
 Union Select ID = '6', UID = cast ('00000000-0000-1100-0000-000000000000' as uniqueidentifier)
 Union Select ID = '5', UID = cast ('00000000-0011-0000-0000-000000000000' as uniqueidentifier)
 Union Select ID = '4', UID = cast ('00000000-1100-0000-0000- 000000000000' as uniqueidentifier)
 Union Select ID = '3', UID = cast ('00000011-0000-0000-0000-000000000000' as uniqueidentifier)
 Union Select ID = '2', UID = cast ('00001100-0000-0000-0000-000000000000' as uniqueidentifier)
 Union Select ID = '1', UID = cast ('00110000-0000-000 0-0000-000000000000' as uniqueidentifier)
 Union Select ID = '0', UID = cast ('11000000-0000-0000-0000-000000000000' as uniqueidentifier)
)
Select * From UIDs Order By UID desc

Position by highest -to-lowest value:

ABCDEF | 8 9 | 7 6 | 5 4 | 3 2 1 0

Using uuid_generate_v1() on postgresql has Is any order guaranteed?

If yes, it does not matter whether it is guaranteed to generate the UUID on each machine or on which machine? Since V1 UUID is a timestamp MAC address, Postgres is sorted internally by the timestamp part, and then sorted by MAC?

Can I “order” a UUID type column and expect it to always work (seems to work)?

I want to generate UUIDs on multiple machines (using postgresql uuid_generate_v1()), copy them to a Postgres instance, and then sort by the UUID column. It must be guaranteed to be sorted by machine, not all machines UUID order.

Although it is by no means a clear answer-ie. “Is it the behavior defined in all PostgreSQL installations?”, this SQL (for SQL Server ) Check the order of each byte in the GUID. Some adjustments may be required to PostgreSQL.

Generating such a mapping should allow people to view the specific UUID structure (one of the clearly defined types) in PostgreSQL One or other) “Order in a specific way”.

With UIDs As (-- 0 1 2 3 4 5 6 7 8 9 ABCDEF
 Select ID = ' F', UID = cast ('00000000-0000-0000-0000-000000000011' as uniqueidentifier)
 Union Select ID ='E', UID = cast ('00000000-0000-0000-0000-000000001100' as uniqueidentifier )
 Union Select ID ='D', UID = cast ('00000000-0000-0000-0000-000000110000' as uniqueidentifier)
 Union Select ID ='C', UID = cast ('00000000- 0000-0000-0000-000011000000' as uniqueidentifier)
 Union Select ID ='B', UID = cast ('00000000-0000-0000-0000-001100000000' as uniqueidentifier)
 Union Select ID = ' A', UID = cast ('00000000-0000-0000-0000-11 0000000000' as uniqueidentifier)
 Union Select ID = '9', UID = cast ('00000000-0000-0000-0011-000000000000' as uniqueidentifier)
 Union Select ID = '8', UID = cast ('00000000-0000-0000-1100-000000000000' as uniqueidentifier)
 Union Select ID = '7', UID = cast ('00000000-0000-0011-0000-000000000000' as uniqueidentifier)
 Union Select ID = '6', UID = cast ('00000000-0000-1100-0000-000000000000' as uniqueidentifier)
 Union Select ID = '5', UID = cast ('00000000-0011-0000-0000- 000000000000' as uniqueidentifier)
 Union Select ID = '4', UID = cast ('00000000-1100-0000-0000-000000000000' as uniqueidentifier)
 Union Select ID = '3', UID = cast ('00000011-0000-0000-0000-000000000000' as uniqueidentifier)
 Union Select ID = '2', UID = cast ('00001100-0000-0000-0000-000000000000' as uniqueidentifier)
 Union Select ID = '1', UID = cast ('00110000-0000-0000-0000-000000000000' as uniqueidentifier)
 Unio n Select ID = '0', UID = cast ('11000000-0000-0000-0000-000000000000' as uniqueidentifier)
)
Select * From UIDs Order By UID desc

In SQL Server (2014, and matches that in SQL Server 2005), the descending order is:

Position by highest-to-lowest value:

ABCDEF | 8 9 | 7 6 | 5 4 | 3 2 1 0

Since SQL Server’s newsequentialid uses this sequence for index-friendly GUID generation, the behavior may never change. SQL Server also This behavior must be maintained in all systems to support replication. So if the question is about SQL Server, I would definitely say “there is a consistent GUID ordering in SQL Server”, this can definitely depend on SQL Server.

However, this sorting is different from the GUID sorting of .NET, and I would not be surprised if the sorting in PostgreSQL is different. The “flip” difference in SQL Server is because it follows the “Variant 2” of COM GUIDs (again Little-endian) sorting; even for “Variant 1” UUIDs. (However, why do these organizations themselves seem to be sorted more casually from right to left: more Microsoft history? )

The interesting question remains: which/how to specify in PostgreSQL? If it is not explicitly specified, can realization still be regarded as an axiom of behavior?

Also see this question for more details about SQL Server’s UUIDs; delicious details on why these differences exist.