In PostgreSQL, how to generate random unique integer number for column, return which not exits in table column?
Add a comment
|
1 Answer
See the pseudo_encrypt function, which implements a permutation based on the Feistel network technique. Combined with a postgres sequence, this guarantees unicity of the result, as well as randomness to the human eye.
Example:
CREATE OR REPLACE FUNCTION pseudo_encrypt(VALUE int) returns int AS $$
DECLARE
l1 int;
l2 int;
r1 int;
r2 int;
i int:=0;
BEGIN
l1:= (VALUE >> 16) & 65535;
r1:= VALUE & 65535;
WHILE i < 3 LOOP
l2 := r1;
r2 := l1 # ((((1366 * r1 + 150889) % 714025) / 714025.0) * 32767)::int;
l1 := l2;
r1 := r2;
i := i + 1;
END LOOP;
RETURN ((r1 << 16) + l1);
END;
$$ LANGUAGE plpgsql strict immutable;
create sequence seq maxvalue 2147483647;
create table tablename(
id int default pseudo_encrypt(nextval('seq')::int),
[other columns]
);
A variant with a 64-bit output space can be found at: pseudo_encrypt() function in plpgsql that takes bigint.
EDIT: pseudo_encrypt implements only one permutation, and it does not accept a user-supplied key. If you prefer having your own permutations, depending on secret keys, you may consider skip32 (a 32-bit block cipher based on Skipjack, with 10 bytes wide keys).
A plpgsql function (ported from Perl/C) is available at: https://wiki.postgresql.org/wiki/Skip32
5 Comments
Amol Kshirsagar
should nt it be int instead of bigint for the id column.Means I understand that the seq wont return anything beyond the int range but is there any specific reason to make it bigint instead of int?
create table tablename( id int default pseudo_encrypt(nextval('seq')::int), [other columns] );
Daniel Vérité
@AmolKshirsagar: yes, it could be an integer now. When initially answered, pseudo_encrypt returned a bigint, but it has been modified since. The answer is now updated accordingly.
Amol Kshirsagar
Daniel Vérité : Is there a way to test out this approach using some script. I have made the changes. Just wanted to be 100% sure that I don't make a mistake in production since that would be serious issue
Daniel Vérité
@AmolKshirsagar: well you could call this function with the 4 billion possible inputs or a fraction thereof and check the properties (I guess unicity and dispersion).
Edward Brey
Don't use this technique if the goal of randomness is to prevent information disclosure. This technique obscures, but doesn't eliminate, the disclosure, since it uses predictable pseudo-random numbers.