1

Currently 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?

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

3
  • SELECT FLOOR(RANDOM() * 10000000000);
    – Vérace
    Mar 19, 2020 at 19:06
  • I'm doing something similar in my code. I want to do it directly in the database.
    – Cesar
    Mar 19, 2020 at 19:24
  • Can you expand on what you mean exactly by ` I want to do it directly in the database.`? p.s. welcome to the forum!
    – Vérace
    Mar 20, 2020 at 8:52

1 Answer 1

1

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.

Example

CREATE EXTENSION permuteseq;

CREATE SEQUENCE seqname MAXVALUE 9999999999; -- 10 digits

CREATE TABLE example(
 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.