How to generate random string for all rows in postgres

Question

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 = 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. How can I make it to generate one random string for each row?

I know I can make it a function like this:

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

and then call the function in the update query. But I'd prefer to do it without a function.

Could you please refer this [link](https://stackoverflow.com/questions/3970795/how-do-you-create-a-random-string-thats-suitable-for-a-session-id-in-postgresql) — gks, Jan 04 '21 at 10:20
Because using a custom function is the cleanest way to do this. — a_horse_with_no_name, Jan 04 '21 at 14:45

score 1 · Accepted Answer · answered Jan 04 '21 at 12:38

The problem is that the Postgres optimizer is just too smart and deciding 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.

One way to get around this is to use a correlated subquery. Here is an example:

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'
                                   ), '');

Here is a db<>fiddle.

How to generate random string for all rows in postgres

1 Answers1