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

Got it. Many thanks.

On Mon, Jan 4, 2021 at 2:46 PM hubert depesz lubaczewski <depesz(at)depesz(dot)com>
wrote:

> 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
>
>