Обсуждение: How to generate random string for all rows in postgres
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 (here):
The problem is that the Postgres optimizer is just too smart and decides 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.
Is this (specifically the point about random()) a bug or feature? Thanks.
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
Got it. Many thanks.
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 :)
Best regards,
depesz