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?

Why does the integer need to be random instead of regular seeded, incrementing integer? Random won't (especially out of 20) guarantee uniqueness. This clarification of requirements could help in determining the solution. — MikeSmithDev
– MikeSmithDev, Commented Jan 2, 2014 at 19:19
Do you mean 20 numbers? Or 20 digit numbers? i.e. do you want to pick a random number from 0 to 20, or pick a number like 123458888812111142123? — Craig Ringer
– Craig Ringer, Commented Jan 2, 2014 at 19:22

Craig Ringer · Accepted Answer · 2014-07-02 16:01:07Z

23

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;

edited Jul 2, 2014 at 16:01

answered Jan 2, 2014 at 19:24

Craig Ringer

329k84 gold badges743 silver badges821 bronze badges

Sign up to request clarification or add additional context in comments.

14 Comments

Daniel Vérité Over a year ago

Should you need the 64 bits variant (or 19 decimal digits), get it from pseudo_encrypt() function in plpgsql that takes bigint

nakhli Over a year ago

how would you seed the function?

Craig Ringer Over a year ago

@Sinbadsoft.com It isn't an iterative pseudo random generator. It doesn't take a "seed" as such. It maps inputs to outputs 1:1 in a random seeming manner.

nakhli Over a year ago

@CraigRinger I get that it is a pseudo random generator, and as such I expect it to take a seed. en.wikipedia.org/wiki/Pseudorandom_number_generator

Craig Ringer Over a year ago

@Sinbadsoft.com Er... I just said that it is not an iterative pseudo random generator. It's a feistel cipher or network, a 1:1 mapping of input to output. The network parameters (in the function body) control the particular mapping used and are as close to a "seed" as it has.

|

Collectives™ on Stack Overflow

PostgreSQL - random primary key

1 Answer 1

14 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

14 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related