How to generate random string for all rows in postgres

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.

Responses

Browse pgsql-general by date

  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