PostgreSQL批量更新用户表时每行随机值失效问题排查与解决

你写的标量子查询（无关联主表的子查询）在PostgreSQL的UPDATE语句中只会执行一次，random()函数仅生成一个随机值，导致所有符合条件的用户被统一设置为同一个gender值。哪怕你添加了generate_series(1,1)，这个子查询依然是独立的标量子查询，仍只会执行一次，结果自然没有变化。

以下几种方法都能实现为每个用户分配随机gender的需求：

• 直接移除子查询，将随机表达式写在SET后
  这种方式最简洁，PostgreSQL会为每一行单独计算random()值：

  UPDATE users
  SET gender = (array['male', 'female'])[floor(random() * 2) + 1]
  WHERE id > 0;
  

• 使用关联子查询，强制每行执行一次计算
  通过主表的字段关联子查询，让子查询为每一行单独生成随机值：

  UPDATE users u
  SET gender = (
      SELECT (array['male', 'female'])[floor(random() * 2) + 1]
      FROM users u2
      WHERE u2.id = u.id
  )
  WHERE id > 0;
  

• 通过FROM子句关联自身
  关联主表自身，确保每行都会重新计算随机值：

  UPDATE users
  SET gender = (array['male', 'female'])[floor(random() * 2) + 1]
  FROM users u
  WHERE users.id = u.id AND users.id > 0;
  

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