Re: How to generate random string for all rows in postgres

On Mon, Jan 04, 2021 at 02:14:11PM +0100, Hassan Akefirad wrote:
> 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 ([1]here):

Hi,
first of all - there is no need to use array_to_string(array( ... ))

just bar = (select string_agg).

it will not work, for the reasons you said, but it's better not to
overcomplicate stuff.

For your case, I think I'd simply make a function for generating random
strings:

CREATE OR REPLACE FUNCTION random_string( int ) RETURNS TEXT as $$
    SELECT string_agg(substring('0123456789bcdfghjkmnpqrstvwxyz', round(random() * 30)::integer, 1), '') FROM generate_series(1, $1);
$$ language sql;

And then use it like this:

update foo set bar = random_string(9)

I know it's not perfect, but:
1. it works
2. your query becomes easier to read/understand
3. as a side benefit you will get function for other use cases :)

Best regards,

depesz