9

In PostgreSQL, how to generate random unique integer number for column, return which not exits in table column?

frlan
  • 6,950
  • 3
  • 31
  • 72
PratikShah
  • 153
  • 1
  • 7

1 Answers1

16

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

Daniel Vérité
  • 58,074
  • 15
  • 129
  • 156
  • 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] );` – Amol Kshirsagar Jun 24 '20 at 08:57
  • @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. – Daniel Vérité Jun 24 '20 at 09:15
  • 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 – Amol Kshirsagar Jul 08 '20 at 22:02
  • @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). – Daniel Vérité Jul 09 '20 at 10:48
  • Don't use this technique if the goal of randomness is to prevent [information disclosure](https://softwareengineering.stackexchange.com/a/328534/85530). This technique obscures, but doesn't eliminate, the disclosure, since it uses predictable pseudo-random numbers. – Edward Brey Mar 13 '21 at 10:27