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) — thar45, Jan 04 '21 at 10:20
Because using a custom function is the cleanest way to do this. — , Jan 04 '21 at 14:45

score 2 · 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.

Jake · Answer 2 · 2023-02-16T22:44:39.133

For a random mixed-case numeric-inclusive string containing up to 32 characters use:

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

and replace XXX with the length of desired string plus one. To replace all with length 32 strings, Example:

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

14235ccd21a408149cfbab0a8db19fb2 might be a value that fills one of the rows. Each row will have a random string but not guaranteed to be unique.

For generating strings with more than 32 characters

Just combine the above with a CONCAT

How to generate random string for all rows in postgres

2 Answers2