Postgresql – For Postgres, how would I define a custom function to generate primary keys

functionspostgresqlprimary-key

In postgres, primary keys are typically generated using a SEQUENCE. I am hoping to generate a unique random 12 character string for use as a primary key.
How would I do this in Postgres?

Best Answer

Turning the comments into an Answer so we can close this Question.

12-chars not random enough

A 12-character random string may be a poor choice for a surrogate key. Your generated values might have a significant chance of colliding, especially depending on your particular implementation for generating such values.

No need to invent such an identifier, as the industry has defined one already: UUID.

UUID

A better route would be using a standard Universally Unique Identifier (UUID). A UUID is a 128-bit value. Though usually displayed as a string of thirty-two hexadecimal characters plus four hyphens (36 chars total), Postgres knows how to compactly store a UUID natively as 128-bit. Postgres also supports indexing a UUID column, so the UUID type serves well as a primary key.

The original version of UUIDs are unique in space and time by combining the generating computer’s MAC address, the current moment, plus a small random number. You may choose alternative versions if security or privacy concerns require that you not encode the place (MAC address) or the time (current moment).

One advantage of UUIDs is that they may be generated either on the server-side and/or the client-side (within your app).

An advantage of using standard UUIDs instead of roll-your-own values is that all mainstream operating systems and development platforms already have high-quality implementations for generating quality UUIDs. No need to implement and document your roll-your-own algorithm.

Postgres has built-in support for storing UUID values. To generate UUID values, you need a plug-in, an “extension”. The uuid-ossp extension is usually bundled with a distribution of Postgres, but you may need to activate it.

For more info, see the Question, Generating a UUID in Postgres for Insert statement?.