How to generate random unique number in PostgreSQL using function

Question

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

Answer 1

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