CREATE FUNCTION random_string(randomLength int)įROM generate_series(1,randomLength) AS gs(x)Īnd then SELECT * FROM random_string(10) SELECT array_to_string(įrom here we can even turn it into a function. Then we wrap it in an ARRAY constructor (because this is fast) SELECT ARRAY(Īnd, we call array_to_string() to get a text. Now we need a range between 1 and 63 SELECT trunc(random()*62+1)::int+1 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', First we demonstrate that we can take the random-range and pull from it. The function substring(string ) looks useful.Id TEXT PRIMARY KEY DEFAULT ('user_' || generate_uid(10)), Or maybe you want to add a prefix for convenience when looking at a single ID in the logs or in your debugger (similar to how Stripe does it), like so: CREATE TABLE users ( It will automatically generate the id values: id | name |. INSERT INTO users (name) VALUES ('victor') Id TEXT PRIMARY KEY DEFAULT generate_uid(10),Īnd then when inserting data, like so: INSERT INTO users (name) VALUES ('ian') With the function defined, you can use it in a table definition, like so: CREATE TABLE users ( So you will likely want a length greater (or much greater) than 10 for any reasonably commonly created object, I just used 10 as a simple example. When doing this you need to be sure that the length of the IDs you are creating is sufficient to avoid collisions over time as the number of objects you've created grows, which can be counter-intuitive because of the Birthday Paradox. Output := output || substr(characters, get_byte(bytes, i) % l + 1, 1) Īnd then to run it simply do: generate_uid(10) Var set = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789' įigured this out, here's a function that does it: CREATE OR REPLACE FUNCTION generate_uid(size INT) RETURNS TEXT AS $$Ĭharacters TEXT := 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789' Here's what the Node.js code for doing it in the application layer might look like: var crypto = require('crypto') I'm currently doing this in the application layer, but I'm looking to move it into the database layer to reduce interdependencies. This requirement is also why encode(gen_random_bytes(), 'hex') isn't quite right for this case, since it reduces the character set and thus forces me to increase the length of the strings to avoid collisions. I'm looking for something that gives me IDs similar to what Stripe (or others) use, that look like: "id": "ch_19iRv22eZvKYlo2CAxkjuHxZ" that are as short as possible while still containing only alphanumeric characters. I know there's gen_random_uuid() for UUIDs, but I don't want to use them in this case. reducing the string size for usability.įrom what I can tell, it must use gen_random_bytes() instead of random() for true randomness, to reduce the chance that they can be guessed. Where size is customizable depending on your own tradeoffs for lowering the chance of collisions vs. I'm thinking that ideally the solution has a signature similar to: generate_uid(size integer) returns text How can you generate them randomly and safely (as far as reducing collisions and reducing predictability goes) with an easy way to specify different lengths for different use cases, in Postgres 9.6+? Given IDs which contain a-zA-Z0-9, and vary in size depending on where they're used, like. 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.
0 Comments
Leave a Reply. |