I have foo table and would like to set bar column to a random string. I've got the following query:
update foo
set bar = array_to_string(
array(select string_agg(substring('0123456789bcdfghjkmnpqrstvwxyz', round(random() * 30)::integer, 1), '')
from generate_series(1, 9)), '');
But it generates the random string once and reuse it for all rows. I asked people on SO and one of the giants answered (here):
The problem is that the Postgres optimizer is just too smart and decides that it can execute the subquery only once for all rows. Well -- it is really missing something obvious -- the random() function makes the subquery volatile so this is not appropriate behavior.
Is this (specifically the point about random()) a bug or feature? Thanks.
