From: | Hassan Akefirad <akefirad(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | How to generate random string for all rows in postgres |
Date: | 2021-01-04 13:14:11 |
Message-ID: | CAHRWLaEg3pmZc3gjTby9cGMO7mavyAWftuBrSBqcGRaxW7eBhQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
<https://stackoverflow.com/a/65562867/2194119>):
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.
From | Date | Subject | |
---|---|---|---|
Next Message | hubert depesz lubaczewski | 2021-01-04 13:46:57 | Re: How to generate random string for all rows in postgres |
Previous Message | Thiemo Kellner | 2021-01-04 10:08:08 | Re: Possible trigger bug? function call argument literalised |