在PostgreSQL中，用列表中的值随机填充一个表列

Question

我想用以下值随机填充表中的一列：可用、暂停、部分首付、已售出/不可用、代币

我尝试过使用以下命令，但它会用得到的第一个值填充整个列。

update mytable
set sold_status = (select (array['Available', 'On Hold', 'Partial Downpayment', 'Sold/Unavailable', 'Token Recieved'])[floor(random() * 5 + 1)])

我知道如何用编程语言实现这一点，但我更喜欢PostgreSQL查询来实现这一目标。

Answer 1

PostgreSQL跟踪简单表达式的波动性，但不跟踪不包含外部查询引用的子选择的波动性。你可以通过引入对外部查询的人工引用来解决这个问题，但在你的情况下，你一开始就不需要子选择，所以就去掉它

update mytable
set sold_status = (array[
'Available', 'On Hold', 'Partial Downpayment', 'Sold/Unavailable', 'Token Recieved'
]) [floor(random() * 5 + 1)];

Answer 2

Postgres认为只运行random()一次是在帮你忙——忘记了random()是一个易失函数。

您可以使用case:修复此问题

update mytable
set sold_status = (case (random()*5)::int when 0 then 'Available' when 1 then 'On Hold' when 2 then 'Partial Downpayment' when 3 then 'Sold/Unavailable' when 4 then 'Token Recieved' end);

这里有一个db<gt；不停摆弄

Answer 3

由于此页面的标题是"Populate a table column ..."，我将在此处放置我的方法，以在列表的列中创建随机值：

CREATE TABLE IF NOT EXISTS cars(
id SERIAL PRIMARY KEY,
brand VARCHAR(30)
);
INSERT INTO cars (
brand
)
SELECT
(array['BMW','AUDI','MERCEDES-BENZ'])[floor(random() * 3 + 1)]
FROM generate_series(1, 10);

它将从品牌列表中创建10行随机值。

Answer 4

接受的答案不正确！

你可以在这里看到这一点(下面的所有代码都在这里(：

CREATE TABLE tab AS
SELECT
id,
NULL AS rand_x5,
NULL AS sold_status
FROM 
generate_series(1, 10000) AS t(id);

然后运行：

WITH r AS
(
SELECT j, (RANDOM() * 5)::INT AS x
FROM GENERATE_SERIES(1, 10000) AS j
)
UPDATE tab
SET 
rand_x5 = r.x, 
sold_status =
CASE r.x                   -- note different brackets (ranges - inclusive/exclusive boundaries)
WHEN 0 THEN 'Available'  -- only happens from [0 - 0.5)    
WHEN 1 THEN 'On Hold'    --      happens from [0.5 - 1.5) &c.
WHEN 2 THEN 'Partial'
WHEN 3 THEN 'Sold'
WHEN 4 THEN 'Token'
END 
FROM r
WHERE tab.id = r.j;

然后运行聚合查询：

SELECT
rand_x5, sold_status, COUNT(rand_x5), COUNT(sold_status)
FROM tab
GROUP by rand_x5, sold_status
ORDER BY rand_x5 DESC NULLS first;

结果：

rand_x5 sold_status count   count
5        null  1047       0
4       Token  2062    2062
3        Sold  1995    1995
2     Partial  1912    1912
1     On Hold  2006    2006
0   Available   978     978
SELECT 6   --  << 6 instead of 5

现在，这有两个主要问题：

• (RANDOM() * 5)::INT的输出中有6个可能的值(0-5(，而不是5。由于您尚未处理CASE表达式中的5，因此sold_status变为NULL

• 第一个值(0(和最后一个值(5(是偏斜的，因为如果你生成大量的值，它们的值数只有其他值(1-4(的1/2，因为它们只在0.5的范围内生成，而其他值则在1 的范围内产生

正确的代码应该是FLOOR((RANDOM() * 5))-请参阅fiddle的底部。运行时的结果是：

rand_x5 sold_status count   count
0   Available   1938    1938
1   On Hold 2048    2048
2   Partial 1983    1983
3   Sold    2001    2001
4   Token   2030    2030

没有NULLs，所有值(在合理范围内(都是相等平衡的！(RANDOM() * 5)::INT中的CAST到INT与ROUND((函数的作用相同，导致错误。FLOOR总是向下取整，并且不会出现此问题。