On Mon, Jan 4, 2021 at 2:46 PM hubert depesz lubaczewski <depesz@depesz.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 :)