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?
Postgresql – For Postgres, how would I define a custom function to generate primary keys
functionspostgresqlprimary-key
Related Question
- What could justify a non explicit – non atomic primary key
- Postgresql – Automatic index creation for primary vs. foreign keys in Postgresql
- Postgresql – Best practices for generating unique multi-column keys for weak entities
- How do Databases Ensure Uniqueness of Primary Keys, Particularly UUID Keys
- Postgresql – In Postgres 10, how do the choice of SERIAL or UUID type as primary key affect replication, if at all
- Postgresql – How to change Postgres primary key column values
- Postgresql – What are the performance implications of using uuid as primary key in Postgres 10.12? (need canonical answer)
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?.