Postgresql – PgAdmin Autogenerate random number


Hey guys right now I'm passing one value to my database, it's a random and unique 10 digit number.

Is there a way i could generate this number directly in my database each time a new row is created?

I'm Using PostgreSQL (pgadmin)

It looks something like this

id_Primarykey  Name  random number  
1              x     1231233592  
2              Y     5234234837  
3              Z     3453450394

Said number has to be unique, 10 digits, and random (non sequential)

Best Answer

Getting unique random looking numbers in a given interval

If you can install C code into your database, the permuteseq extension provides a ready-to-use solution to this problem. It encrypts consecutive numbers with format-preserving encryption techniques to get both the non-guessability, the unicity (it's collision-free), and the confinment in a given output range.

To auto-assign the values on insertion, the simplest way is to set the column's DEFAULT clause.



CREATE SEQUENCE seqname MAXVALUE 9999999999; -- 10 digits

 id serial,
 col text,
 random_number bigint default
     permute_nextval('seqname'::regclass, 8784354463) -- or any random number

INSERT INTO example(id,col) VALUES(1, 'foo');
INSERT INTO example(id,col) VALUES(2, 'bar');
INSERT INTO example(id,col) VALUES(3, 'baz');

SELECT * FROM example;

 id | col | random_number 
  1 | foo |    9737615019
  2 | bar |    7519592769
  3 | baz |    8112392939