PostgreSQL - random primary key

Question

I need a primary key for a PostgreSQL table. The ID should consist out of a number from about 20 numbers.

I am a beginner at database and also worked not with PostgreSQL. I found some examples for a random id, but that examples where with characters and I need only an integer.

Can anyone help me to resolve this problem?

Answer 1

I'm guessing you actually mean random 20 digit numbers, because a random number between 1 and 20 would rapidly repeat and cause collisions.

What you need probably isn't actually a random number, it's a number that appears random, while actually being a non-repeating pseudo-random sequence. Otherwise your inserts will randomly fail when there's a collision.

When I wanted to do something like this a while ago I asked the pgsql-general list, and got a very useful piece of advice: Use a feistel cipher over a normal sequence. See this useful wiki example. Credit to Daniel Vérité for the implementation.

Example:

postgres=# SELECT n, pseudo_encrypt(n) FROM generate_series(1,20) n;
 n  | pseudo_encrypt 
----+----------------
  1 |     1241588087
  2 |     1500453386
  3 |     1755259484
  4 |     2014125264
  5 |      124940686
  6 |      379599332
  7 |      638874329
  8 |      898116564
  9 |     1156015917
 10 |     1410740028
 11 |     1669489846
 12 |     1929076480
 13 |       36388047
 14 |      295531848
 15 |      554577288
 16 |      809465203
 17 |     1066218948
 18 |     1326999099
 19 |     1579890169
 20 |     1840408665
(20 rows)

These aren't 20 digits, but you can pad them by multiplying them and truncating the result, or you can modify the feistel cipher function to produce larger values.

To use this for key generation, just write:

CREATE SEQUENCE mytable_id_seq;

CREATE TABLE mytable (
    id bigint primary key default pseudo_encrypt(nextval('mytable_id_seq')),
    ....
);

ALTER SEQUENCE mytable_id_seq OWNED BY mytable;