随机默认列值会在发生冲突时重新计算 [英] Random default column value that rerolls on conflict

问题描述

我有一个列，希望将 default 设置为指定范围内随机生成的 int8 。我也希望此列是唯一的，因此如果生成的随机值已经存在，则应重新滚动它。

I have a column that I would like to default to a randomly generated int8 in a specified range. I would also like this column to be unique, so if a random value is generated that already exists, it should be rerolled.

所以我的问题是最惯用的方法是什么上面的操作是在PostgreSQL中进行的，理想情况下具有良好的性能并支持批量插入。

So my question is what the most idiomatic way to do the above is in PostgreSQL, ideally with good performance and supporting bulk inserts.

例如，如果我有一个 Person 具有列 name 和 id 的表，而我想要 id 为（0，999999）范围内的随机唯一 int8 。我希望能够插入 Paul ， Kelly ， David 和 Katie 并获得以下内容：

For example if I had a Person table with columns name and id, and I want id to be a random unique int8 in the range (0, 999999). I would want to be able to insert Paul, Kelly, David and Katie and get something like the following:

| Name  |   id   |
+-------+--------+
| Paul  | 314563 |
| Kelly | 592103 |
| David | 127318 |
| Katie | 893134 |

没有重复的风险，也没有插入失败的风险。

With no risk of duplicates and no risk of an insertion failure.

范围不会太大，以至于我无法确信它们永远不会发生碰撞（即生日悖论）。

The range is not going to be large enough for me to safely assume they will never collide (i.e Birthday Paradox).

我还应该说我确实想要真正的不可预测的随机性，所以序列上的密码不会计算在内。

I should also say I do want true unpredictable randomness, so a cipher on a sequence would not count.

关于如何生成随机数的答案很多，因此，问题是唯一性方面。

There are a variety of answers on how to generate random numbers, so the main focus of the question is the uniqueness aspect.

这样说来，一种干净有效的方法可以在一个统一的环境中统一生成 int8 任意大范围将不胜感激。 random（）* n 开始在 n> 2 ^ 53 （也许更早）。

With that said a clean and efficient way to generate an int8 uniformly in an arbitrarily large range would be appreciated. random() * n starts having gaps when n > 2 ^ 53 (perhaps earlier).

推荐答案

可能的解决方案：

create table t (name varchar (50), id int);

-- 1. generate a list of possible ids
-- 2. cast the id in varchar to make a string after that
-- 3. aggregate all the possible ids in a string with a ',' separator
-- 4. make the string a list
-- 5. select a random value in this list
-- 6. insert the new id for the wanted name. Here 'test'
with cte as 
(
  SELECT a.n as possible_id
  from generate_series(1, 150000) as a(n)
  where not exists (select 1 from t where t.id = a.n)
)
, cte_s as 
(
  select 
    (
        string_to_array( 
            string_agg( 
                cast(possible_id as varchar)
                , ','
            )
            , ','
        )
    )[floor(random() * 150000 + 1)] as new_id
  from cte
)
insert into t
values ('test', (select new_id from cte_s)::int); 

-- test that your code doing what you want
select *
from t;

http://sqlfiddle.com/#!17/17d42/26

您可以根据需要修改最大金额当然。

You can modify the maximum amount as you want of course.

这篇关于随机默认列值会在发生冲突时重新计算的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！