PostgreSQL uses the standardized UUID generation algorithms provided by ITU-T Rec. X.667, ISO/IEC 9834-8:2005, and RFC 4122. From the docs on uuid-ossp
,
The uuid-ossp module provides functions to generate universally unique identifiers (UUIDs) using one of several standard algorithms. There are also functions to produce certain special UUID constants.
uuid_generate_v1()
This function generates a version 1 UUID. This involves the MAC address of the computer and a time stamp. Note that UUIDs of this kind reveal the identity of the computer that created the identifier and the time at which it did so, which might make it unsuitable for certain security-sensitive applications.
So long as the MAC address does not change, you'll be golden.
That all said, I agree with @a_horse_with_no_name,
From my understanding this is only necessary in SQL Server because tables are stored in a clustered index which makes random insertions slower then with a heap table. Postgres has no such concept, so I don't think that would make a difference in Postgres
In fact, given the chance of fewer collisions and more security, I would take it. And to that I would use uuid_generate_v4()
uuid_generate_v4()
This function generates a version 4 UUID, which is derived entirely from random numbers.
RFC4122 documentation (section 4.1.6) specifies:
For UUID version 1, the node field consists of an IEEE 802 MAC
address, usually the host address. For systems with multiple IEEE
802 addresses, any available one can be used. The lowest addressed
octet (octet number 10) contains the global/local bit and the
unicast/multicast bit, and is the first octet of the address
transmitted on an 802.3 LAN.
For systems with no IEEE address, a randomly or pseudo-randomly
generated value may be used; see Section 4.5. The multicast bit must
be set in such addresses, in order that they will never conflict with
addresses obtained from network cards.
If I interpret this correctly, I'd say: a random Multicast address is any randomly generated MAC adress which has just the multicast bit set. The multicast bit is just one of the bits from the node part of a UUID (for all practical purposes, this just forces one specific bit of the UUID to be set).
Side Notes
I don't think you actually can specify a MAC address to the PostgreSQL function. If it follows the RFC, the function(s) must either use any of the MAC addresses available in your system, or a random one (with a specific bit set).
Whether this random value is always the same for a specific machine (which wouldn't look very random to me) or is just purely (pseudo)random and changing every time, is not clear from this explanation... but can be very easily tested:
SELECT uuid_generate_v1mc() AS u1, uuid_generate_v1mc() AS u2
gets me right now:
'91ccbe0c-488c-11e7-8d61-b7a8bb0bd0e3','91cd902a-488c-11e7-8d61-8bdf8f55ae02'
This translates (using the PERL program from https://stackoverflow.com/questions/1709600/what-kind-of-data-can-you-extract-from-a-uuid) to
time: Sat Jun 3 20:43:43 2017 +682.51ms
clock id: 36193
Mac: b7:a8:bb:0b:d0:e3
broadcast/multicast bit set.
and
time: Sat Jun 3 20:43:43 2017 +687.889ms
clock id: 36193
Mac: 8b:df:8f:55:ae:02
broadcast/multicast bit set.
... so, the MAC are actually completely (pseudo)random.
As pointed out by @EvanCarrol: I also think you're better off with v4 UUIDs, I don't think you'll get less collision risk with a randomly generated MAC.
Besides, very many network devices (routers, switches, etc.) have programmable MAC addresses (this is very handy when you want to replace one broken device by another, and make sure all the other devices don't notice any difference at all). This, somehow, makes the MACs not as unique as you probably thought.
Alternatvies: If you work with Windows, may be this tool can let you fake a MAC address. I've not tried it myself, so, "no strings attached".
Best Answer
tl;dr
Call
DEFAULT
when defining a column to invoke one of the OSSP uuid functions. The Postgres server will automatically invoke the function every time a row is inserted.If you already use the pgcrypto extension, consider the Answer by bpieck.
Plugin Required To Generate UUID
While Postgres out-of-the-box supports storing UUID (Universally Unique Identifier) values in their native 128-bit form, generating UUID values requires a plug-in. In Postgres, a plug-in is known as an
extension
.To install an extension, call
CREATE EXTENSION
. To avoid re-installing, addIF NOT EXISTS
. See my blog post for more details, or see this page in StackOverflow.The extension we want is an open-source library built in C for working with UUIDs, OSSP uuid. A build of this library for Postgres is often bundled with an installation of Postgres such as the graphical installers provided by Enterprise DB or included by cloud providers such as Amazon RDS for PostgreSQL.
Generating Various Kinds Of UUID
See the extension’s doc to see a list of multiple commands offered for generating various kinds of UUID values. To get the original version of UUID built from the computer’s MAC address plus current date-time plus a small random value, call
uuid_generate_v1()
.Later variations on this theme were developed for alternate kinds of UUIDs. Some people may not want to record the server’s actual MAC address, for example, for security or privacy concerns. The Postgres extension generates five kinds of UUIDs, plus the “nil” UUID
00000000-0000-0000-0000-000000000000
.UUID As Default Value
That method call can be made automatically to generate a default value for any newly inserted row. When defining the column, specify:
See that command used in the following example table definition.
UUID versions
The uuid-ossp plugin can generate various versions of UUID.
uuid_generate_v1()
Contains MAC address of current computer + current moment. Commonly used, but avoid if you are sensitive about disclosing the MAC of your database server or the time when this value was generated. Defined by specification as a Version 1 UUID.
uuid_generate_v1mc()
Like Version 1, but with a random multicast MAC address instead of the real MAC address. Apparently a way to use Version 1 but substituting another MAC rather than the actual MAC of your database server if you are sensitive about disclosing that fact.
What is a ‘random multicast MAC’? I do not know exactly. After reading section 4.1.6 of RFC 4122, I suspect this is a random number used in place of the MAC but with bits set to indicate a multicast MAC address rather than the usual unicast so as to distinguish this variation of Version 1 from a usual real-MAC Version 1 UUID.
uuid_generate_v3( namespace uuid, name text )
Contains an MD5 hash of text you provide. Defined by specification as a Version 3 UUID, namespace-based UUID.
uuid_generate_v4()
Based on randomly-generated data for 121-122 of the 128 bits. Six or seven bits used to indicate Version & Variant. This kind of UUID is practical only if implemented with a cryptographically-strong random generator. Defined by specification as a Version 4 UUID.
uuid_generate_v5( namespace uuid, name text )
Same as Version 3 but using SHA1 hashing. Defined by specification as Version 5 UUID.
uuid_nil()
A special case, all bits set to zero
00000000-0000-0000-0000-000000000000
. Used as a flag for an unknown UUID value. Known as a nil UUID.To compare types, see Question, Which UUID version to use?
If you are interested only in Version 4 (randomly generated), and are already using pgcrypto, see the Answer by bpieck.
If you are curious about Versions 3 & 5, see this Question, Generating v5 UUID. What is name and namespace?.
For more discussion, see my Answer to a similar Question and my blog post UUID values from JDBC to Postgres.