如何在PostgreSQL中为所有行生成随机字符串

Question

我有一个名为foo的表格，想将其中的bar列设置为随机字符串。我已经有了以下查询语句：

update foo
set bar = select string_agg(substring('0123456789bcdfghjkmnpqrstvwxyz', round(random() * 30)::integer, 1), '')
          from generate_series(1, 9);

但它只生成一个随机字符串并重复使用于所有行。我该如何让它为每一行生成一个随机字符串？

我知道我可以将它写成一个函数，像这样：

create function generate_bar() returns text language sql as $$
  select string_agg(substring('0123456789bcdfghjkmnpqrstvwxyz', round(random() * 30)::integer, 1), '')
  from generate_series(1, 9)
$$;

然后在更新查询中调用该函数。但我更喜欢不使用函数来完成。

- Rad

---

请您查看此链接：链接 - undefined

我不明白。为什么我要参考这个链接？ - undefined

因为使用自定义函数是最干净的方法来完成这个任务。 - user330315

对的。创建为一个函数。 - undefined

Answer 1

问题在于Postgres优化器太聪明了，决定它可以为所有行仅执行一次子查询。嗯--它确实错过了一些显而易见的东西--random()函数使子查询不稳定，因此这不是适当的行为。

解决这个问题的一种方法是使用相关子查询。以下是一个示例：

update foo
    set bar = array_to_string(array(select string_agg(substring('0123456789bcdfghjkmnpqrstvwxyz', round(random() * 30)::integer, 1), '')
                                    from generate_series(1, 9)
                                    where foo.bar is distinct from 'something'
                                   ), '');

这是与编程相关的db<>fiddle，请点击这里。

- Gordon Linoff

Answer 2

对于包含数字且大小写混合的随机字符串，最多包含32个字符，请使用以下代码：

UPDATE "foo" SET "bar"= substr(md5(random()::text), 0, XXX);

将XXX替换为所需字符串的长度加一。 要替换所有长度为32的字符串，例如：

UPDATE "foo" SET "bar"= substr(md5(random()::text), 0, 33);

14235ccd21a408149cfbab0a8db19fb2 可能是填充其中一行的值。每一行都会有一个随机字符串，但不一定唯一。

要生成超过 32 个字符的字符串

只需将上述内容与 CONCAT 结合即可。

- Jake