Postgresql – PgAdmin Autogenerate random number

postgresqlrandom

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.

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

Related Solutions

MySQL non-sequential primary key of certain length

I would suggest that the "right direction" would be to go a different direction entirely.

A sufficiently random and difficult-to-guess distribution of keys, assuming that is even an achievable objective, is likely to be otherwise sub-optimal for space and performance. But that's the second problem.

The first problem is that exposing the internals of your database in such a fashion is intrinsically unsafe.

Instead, create a separate column for this "PIN" -- of whatever desired length and data type, with a unique key constraint on the column so no more than one record can possibly have the same PIN associated with it.

You can then populate this value using whatever algorithm you want, and you can subsequently change the algorithm, change the min/max lengths of the value, etc., without any other impact on the rest of your application or database as a result of such changes.

Postgresql – Creating sequence for longer numeric values

If you can live with losing some values to the maximum value, you could combine a sequence with a fixed offset to get the 20 digits. I would also define a check constraint on the table to to make sure that accidental inserts without using the default value insert the wrong value:

create sequence my_sequence_name;
create table foo 
(
   id numeric(20,0) default 10000000000000000001 + nextval('my_sequence_name'),
   constraint check_range check (id between 10000000000000000001 and 99999999999999999999)
);

That will give you a maximum generated value of 19223372036854775808 which doesn't use the complete range of allowed values, but it might be enough for your needs.