如何使用函数在PostgreSQL中生成随机唯一数

Question

在 PostgreSQL 中，如何为列生成随机的唯一整数，使其不在表列中存在？

- PratikShah

Answer 1

参考pseudo_encrypt函数，它基于Feistel网络技术实现置换。与Postgres序列结合使用，可以确保结果唯一性，并对人眼呈现随机性。

示例:

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]
);

可以在以下网址找到输出空间为64位的变体: pseudo_encrypt() function in plpgsql that takes bigint.

---

编辑: pseudo_encrypt 只实现了一种置换，并且不接受用户提供的密钥。如果您想拥有自己的置换，根据秘密密钥，您可以考虑使用 skip32（基于Skipjack的32位块密码，具有10字节宽度的密钥）。

一个从Perl/C移植而来的 plpgsql 函数可以在以下网址找到: https://wiki.postgresql.org/wiki/Skip32

- Daniel Vérité

---

应该将id列设为int而不是bigint。我知道seq不会返回超出int范围的任何内容，但是否有特定原因将其设置为bigint而不是int？创建表tablename( id int 默认伪加密(nextval('seq')::int), [其他列] ); - Amol Kshirsagar

@AmolKshirsagar：是的，现在它可以是一个整数。最初回答时，pseudo_encrypt返回了一个bigint，但是自那以后已经进行了修改。答案现已相应更新。 - Daniel Vérité

Daniel Vérité：有没有一种方法可以使用一些脚本来测试这种方法。我已经做出了更改。只是想百分之百确定我不会在生产中犯错误，因为那将是严重的问题。 - Amol Kshirsagar

@AmolKshirsagar：你可以使用40亿个可能的输入之一来调用此函数，并检查其属性（我猜想是唯一性和分散性）。 - Daniel Vérité

如果随机化的目的是为了防止信息泄露，请勿使用此技术。这种技术可以模糊信息，但并不能消除泄露，因为它使用可预测的伪随机数。 - Edward Brey