How to create a random, unique, alphanumeric identifier of length N in Postgres 9.6+?

Question

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

The specified identifiers containing a-zA-Z0-9, and vary in size depending on where they are used, for example ...

bTFTxFDPPq
tcgHAdW3BD
IIo11r9J0D
FUW5I8iCiS

uXolWvg49Co5EfCo
LOscuAZu37yV84Sa
YyrbwLTRDb01TmyE
HoQk3a6atGWRMCSA

HwHSZgGRStDMwnNXHk3FmLDEbWAHE1Q9
qgpDcrNSMg87ngwcXTaZ9iImoUmXhSAv
RVZjqdKvtoafLi1O5HlvlpJoKzGeKJYS
3Rls4DjWxJaLfIJyXIEpcjWuh51aHHtK

(Similar to the identifiers that Stripe uses .)

How can you generate them randomly and safely (how much less collisions and less predictability) with a simple way to specify different lengths for different use cases, in Postgres 9.6 +?

, , :

generate_uid(size integer) returns text

size .

, , gen_random_bytes() random() , , .

!

---

gen_random_uuid() UUID, . -, , , Stripe ( ), : "id": "ch_19iRv22eZvKYlo2CAxkjuHxZ", , - .

, encode(gen_random_bytes(), 'hex') , , , , .

, , . Node.js :

var crypto = require('crypto');
var set = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789';

function generate(length) {
  var bytes = crypto.randomBytes(length);
  var chars = [];

  for (var i = 0; i < bytes.length; i++) {
    chars.push(set[bytes[i] % set.length]);
  }

  return chars.join('');
}

Answer 1

, , :

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;

:

generate_uid(10)
-- '3Rls4DjWxJ'

---

, , , - . , , ( ), 10 , 10 .

---

, :

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

, :

INSERT INTO collections (name) VALUES ('One');
INSERT INTO collections (name) VALUES ('Two');
INSERT INTO collections (name) VALUES ('Three');
SELECT * FROM collections;

id:

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

---

, , ( Stripe), :

CREATE TABLE collections (
  id TEXT PRIMARY KEY DEFAULT ('col_' || generate_uid(10)),
  name TEXT NOT NULL,
  ...
);

INSERT INTO collections (name) VALUES ('One');
INSERT INTO collections (name) VALUES ('Two');
INSERT INTO collections (name) VALUES ('Three');
SELECT * FROM collections;

      id       |  name  | ...
---------------+--------+-----
col_wABNZRD5Zk | ian    |
col_ISzGcTVj8f | victor |

Answer 2

• 26 [a-z]
• 26 [a-z]
• 10 [0-9]
• 62 [a-zA-Z0-9] (base62)
• substring(string [from int] [for int]) .

, . , .

SELECT substring(
  'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',
  1, -- 1 is 'a', 62 is '9'
  1,
);

1 63

SELECT trunc(random()*62+1)::int+1
FROM generate_series(1,1e2) AS gs(x)

.. .

SELECT substring(
  'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',
  trunc(random()*62)::int+1
  1
)
FROM generate_series(1,1e2) AS gs(x);

ARRAY ( )

SELECT ARRAY(
  SELECT substring(
    'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',
    trunc(random()*62)::int+1,
    1
  )
  FROM generate_series(1,1e2) AS gs(x)
);

array_to_string(), .

SELECT array_to_string(
  ARRAY(
      SELECT substring(
        'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',
        trunc(random()*62)::int+1,
        1
      )
      FROM generate_series(1,1e2) AS gs(x)
  )
  , ''
);

.

CREATE FUNCTION random_string(randomLength int)
RETURNS text AS $$
SELECT array_to_string(
  ARRAY(
      SELECT substring(
        'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',
        trunc(random()*62)::int+1,
        1
      )
      FROM generate_series(1,randomLength) AS gs(x)
  )
  , ''
)
$$ LANGUAGE SQL
RETURNS NULL ON NULL INPUT
VOLATILE LEAKPROOF;

SELECT * FROM random_string(10);

Answer 3

. generate_series, .

SELECT
     string_agg(c, '')
FROM (
     SELECT
          chr(r + CASE WHEN r > 25 + 9 THEN 97 - 26 - 9 WHEN r > 9 THEN 64 - 9 ELSE 48 END) AS c
     FROM (
           SELECT
                 i,
                 (random() * 60)::int AS r
           FROM
                 generate_series(0, 62) AS i
          ) AS a
      ORDER BY i
     ) AS A;

Answer 4

-, " " ( , Youtube ), , - .

, . serial hashids.org PostgreSQL.

• 1:1 ()
• .
• Also base62 [a-zA-Z0-9]

The code looks like this:

SELECT id, hash_encode(foo.id)
FROM foo; -- Result: jNl for 1001

SELECT hash_decode('jNl') -- returns 1001

This module also supports salts.