mardi 31 janvier 2017

How to generate a unique identifier string of length N in Postgres 9.6+?

I've seen a bunch of different solutions on StackOverflow that span many years and many Postgres versions, but with some of the newer features like gen_random_bytes I want to ask again to see if there is a simpler solution in newer versions.

Given IDs which contain a-zA-Z0-9, and vary in size depending on where they're used, like...

3kivYsqh
xysC5hdn
EX41QxTU
YhttHVH5

...or elsewhere...

uXolWvg49Co5EfCo
LOscuAZu37yV84Sa
YyrbwLTRDb01TmyE
HoQk3a6atGWRMCSA

How can you generate them randomly and safely (as far as reducing collisions goes) with an easy way to specify different lengths for different use cases, in Postgres 9.6+?

I'm thinking that ideally the solution has a signature similar to:

generate(length integer) returns text

Where length is customizable depending on your own tradeoffs for lowering the chance of collisions vs. reducing the string size for usability.

Thanks!


PS. I know there's gen_random_uuid() for UUIDs, but I don't want to use them in this case.




Aucun commentaire:

Enregistrer un commentaire