PostgreSQL中冲突重roll的随机唯一int8默认值实现方案

刚好处理过类似的需求，结合PostgreSQL的特性，给你整理一套符合惯用写法、性能可靠的方案，分场景拆解：

一、先打基础：确保唯一性约束 #

不管哪种方案，第一步必须给person表的id列加唯一约束（或者主键），这是保证唯一性的底层保障：

ALTER TABLE person ADD CONSTRAINT person_id_unique UNIQUE (id);

二、小范围场景（如0-999999）的批量插入方案 #

针对你要插入Paul、Kelly、David、Katie的示例，这里有两种实用方法：

方法1：纯SQL批量生成候选ID（高效首选） #

这种方法适合批量插入数量远小于范围总数量的情况，纯SQL实现，符合PostgreSQL惯用写法：

WITH new_names(name) AS (
    VALUES ('Paul'), ('Kelly'), ('David'), ('Katie')
),
available_ids AS (
    -- 生成比需要数量多的随机ID，排除已存在的
    SELECT DISTINCT floor(random() * 999999)::int8 AS id
    FROM generate_series(1, 10) -- 生成10个候选，远大于需要的4个，降低冲突概率
    WHERE floor(random() * 999999)::int8 NOT IN (SELECT id FROM person)
    LIMIT (SELECT COUNT(*) FROM new_names) -- 只取需要的数量
)
INSERT INTO person(id, name)
SELECT a.id, n.name
FROM new_names n
CROSS JOIN LATERAL (SELECT id FROM available_ids LIMIT 1 OFFSET ROW_NUMBER() OVER () - 1) a;

说明一下：generate_series生成足够多的候选ID，用DISTINCT去重+NOT IN排除已存在的ID，最后通过ROW_NUMBER()把唯一ID逐个分配给待插入的名字。只要插入数量远小于范围总量（比如你这里插4条，范围有100万），几乎不会出现候选ID不够的情况，批量插入效率很高。

方法2：自定义函数循环生成（绝对保证成功） #

如果需要绝对保证插入成功（哪怕范围剩余ID不多），可以写个PL/pgSQL函数循环生成：

CREATE OR REPLACE FUNCTION generate_unique_person_id() RETURNS int8 AS $$
DECLARE
    new_id int8;
BEGIN
    LOOP
        new_id := floor(random() * 999999)::int8;
        -- 检查是否存在，不存在则返回
        IF NOT EXISTS (SELECT 1 FROM person WHERE id = new_id) THEN
            RETURN new_id;
        END IF;
    END LOOP;
END;
$$ LANGUAGE plpgsql VOLATILE;

然后批量插入时直接调用：

INSERT INTO person(id, name)
VALUES
    (generate_unique_person_id(), 'Paul'),
    (generate_unique_person_id(), 'Kelly'),
    (generate_unique_person_id(), 'David'),
    (generate_unique_person_id(), 'Katie');

这个函数逻辑很直白：循环生成随机ID，每次生成后检查库里有没有，找到不存在的就返回。好处是绝对能拿到唯一ID，哪怕范围里剩余的ID不多也没问题，适合小批量插入场景。

三、大范围int8的均匀随机生成（解决random()精度问题） #

当你需要生成的范围超过2^53时，random() * n就不靠谱了——因为PostgreSQL的random()返回的是double precision类型，只有53位有效精度，超过这个范围的话，会有很多整数没法被表示出来，导致随机分布出现间隙。这时候我们用pgcrypto扩展的gen_random_bytes()来生成真正的随机字节，转换为int8，完美解决这个问题。

1. 先启用pgcrypto扩展 #

CREATE EXTENSION IF NOT EXISTS pgcrypto;

2. 生成任意范围的均匀随机int8（拒绝采样法） #

以下函数可以生成[min_val, max_val]范围内的均匀随机int8，避免模运算导致的分布偏差：

CREATE OR REPLACE FUNCTION generate_random_int8(min_val int8, max_val int8) RETURNS int8 AS $$
DECLARE
    range_size int8 := max_val - min_val + 1;
    random_bytes bytea;
    random_int int8;
BEGIN
    -- 处理范围无效的情况
    IF min_val > max_val THEN
        RAISE EXCEPTION 'min_val must be <= max_val';
    END IF;

    -- 如果范围只有一个值，直接返回
    IF range_size = 1 THEN
        RETURN min_val;
    END IF;

    LOOP
        -- 生成8字节随机数据，转换成int8
        random_bytes := gen_random_bytes(8);
        random_int := ('x' || encode(random_bytes, 'hex'))::bit(64)::int8;

        -- 只保留在[0, range_size-1]范围内的值，保证分布均匀
        IF random_int >= 0 AND random_int < range_size THEN
            RETURN min_val + random_int;
        END IF;
    END LOOP;
END;
$$ LANGUAGE plpgsql VOLATILE;

为啥用拒绝采样法？因为如果范围大小不是2的幂，直接用模运算（random_int % range_size）会导致某些值出现的概率略高，分布不均匀。拒绝采样法会丢弃超出范围的随机值，只保留符合要求的，这样就能保证每个值的概率完全均匀。而且gen_random_bytes()生成的是真正的随机字节，满足你“不可预测”的要求，比序列加密方案靠谱多了。

3. 结合唯一性的大范围批量插入 #

把上面的随机生成函数和唯一性检查结合，修改成生成唯一大ID的函数（这里默认范围是1到int8的最大值）：

CREATE OR REPLACE FUNCTION generate_unique_large_id(min_val int8 = 1, max_val int8 = 9223372036854775807) RETURNS int8 AS $$
DECLARE
    new_id int8;
BEGIN
    LOOP
        new_id := generate_random_int8(min_val, max_val);
        IF NOT EXISTS (SELECT 1 FROM person WHERE id = new_id) THEN
            RETURN new_id;
        END IF;
    END LOOP;
END;
$$ LANGUAGE plpgsql VOLATILE;

批量插入时直接调用即可：

INSERT INTO person(id, name)
VALUES
    (generate_unique_large_id(), 'Paul'),
    (generate_unique_large_id(), 'Kelly'),
    (generate_unique_large_id(), 'David'),
    (generate_unique_large_id(), 'Katie');

四、性能优化小tips #

• 批量插入量大的话，优先用CTE一次性生成候选ID的方法，减少函数调用的开销。
• 如果要插上千条甚至更多，可以先把已有的ID导入临时表，生成候选ID时用临时表做排除查询，比直接查主表快很多。
• 别忘了id列的唯一约束会自动创建索引，这个索引是保证EXISTS检查和NOT IN查询快的关键，千万别删。

内容的提问来源于stack exchange，提问作者semicolon