How to Generate a Random, Unique, Alphanumeric Id of Length N in Postgres 9.6+

How to generate a random, unique, alphanumeric ID of length N in Postgres 9.6+?

Figured this out, here's a function that does it:

CREATE OR REPLACE FUNCTION generate_uid(size INT) RETURNS TEXT AS $$
DECLARE
  characters TEXT := 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789';
  bytes BYTEA := gen_random_bytes(size);
  l INT := length(characters);
  i INT := 0;
  output TEXT := '';
BEGIN
  WHILE i < size LOOP
    output := output || substr(characters, get_byte(bytes, i) % l + 1, 1);
    i := i + 1;
  END LOOP;
  RETURN output;
END;
$$ LANGUAGE plpgsql VOLATILE;

And then to run it simply do:

generate_uid(10)
-- '3Rls4DjWxJ'

---

Warning

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. 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.

---

Usage

With the function defined, you can use it in a table definition, like so:

CREATE TABLE users (
  id TEXT PRIMARY KEY DEFAULT generate_uid(10),
  name TEXT NOT NULL,
  ...
);

And then when inserting data, like so:

INSERT INTO users (name) VALUES ('ian');
INSERT INTO users (name) VALUES ('victor');
SELECT * FROM users;

It will automatically generate the id values:

    id     |  name  | ...
-----------+--------+-----
owmCAx552Q | ian    |
ZIofD6l3X9 | victor |

---

Usage with a Prefix

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 (
  id TEXT PRIMARY KEY DEFAULT ('user_' || generate_uid(10)),
  name TEXT NOT NULL,
  ...
);

INSERT INTO users (name) VALUES ('ian');
INSERT INTO users (name) VALUES ('victor');
SELECT * FROM users;

      id       |  name  | ...
---------------+--------+-----
user_wABNZRD5Zk | ian    |
user_ISzGcTVj8f | victor |

PostgreSQL Generating Alphanumeric ID from Integer

1) Change int to BigInt

CREATE OR REPLACE FUNCTION public.generate_uid(
    id BigInt,
    min_length integer,
    salt text)
...

2) Change this line:

output := output || substr(shuffle_alphabet, (id % char_length) + 1, 1);

to:

 output := output || substr(shuffle_alphabet::text, ((id % char_length) + 1)::int, 1);

3) And then:

SELECT generate_uid(ceil(extract(epoch from now())*100000)::bigint, 8, '20');

return:

"wExwM7pR" 

Generate unique random strings in plpgsql

There is no repeat statement in plpgsql. Use simple loop.

CREATE OR REPLACE FUNCTION random_token(_table TEXT, _column TEXT, _length INTEGER) RETURNS text AS $$
DECLARE
  alphanum CONSTANT text := 'abcdefghijkmnopqrstuvwxyz23456789';
  range_head CONSTANT integer := 25;
  range_tail CONSTANT integer := 33;
  random_string text;
  ct int;
BEGIN
  LOOP
    SELECT substring(alphanum from trunc(random() * range_head + 1)::integer for 1) ||
      array_to_string(array_agg(substring(alphanum from trunc(random() * range_tail + 1)::integer for 1)), '')
      INTO random_string FROM generate_series(1, _length - 1);
    EXECUTE FORMAT('SELECT count(*) FROM %I WHERE %I = %L', _table, _column, random_string) INTO ct;
    EXIT WHEN ct = 0;
  END LOOP;
  RETURN random_string;
END
$$ LANGUAGE plpgsql;

Note, random_string should be a parameter to format().

Update. According to the accurate hint from Abelisto, this should be faster for a large table:

DECLARE
  dup boolean;
...
    EXECUTE FORMAT('SELECT EXISTS(SELECT 1 FROM %I WHERE %I = %L)', _table, _column, random_string) INTO dup;
    EXIT WHEN NOT dup;
...

Generating an Instagram- or Youtube-like unguessable string ID in ruby/ActiveRecord

You could do something like this:

random_attribute.rb

module RandomAttribute

  def generate_unique_random_base64(attribute, n)
    until random_is_unique?(attribute)
      self.send(:"#{attribute}=", random_base64(n))
    end
  end

  def generate_unique_random_hex(attribute, n)
    until random_is_unique?(attribute)
      self.send(:"#{attribute}=", SecureRandom.hex(n/2))
    end
  end

  private

  def random_is_unique?(attribute)
    val = self.send(:"#{attribute}")
    val && !self.class.send(:"find_by_#{attribute}", val)
  end

  def random_base64(n)
    val = base64_url
    val += base64_url while val.length < n
    val.slice(0..(n-1))
  end

  def base64_url
    SecureRandom.base64(60).downcase.gsub(/\W/, '')
  end
end
Raw

user.rb

class Post < ActiveRecord::Base

  include RandomAttribute
  before_validation :generate_key, on: :create

  private

  def generate_key
    generate_unique_random_hex(:key, 32)
  end
end

Postgres: Get next free id inside a table in a specific range (not using sequences)

It's a classic case of using generate_series and an outer join:

SELECT i FROM t_user RIGHT JOIN generate_series(0,1000) as i ON (c_id=i)
 WHERE c_id is null;

ASP.NET Gridview with Select and its ID being visible or hidden

for this purpose you can use DataKeyNames and DataKeys properties of grid view

---

Related Topics