CODEWITHSUNDEEP
sqldatabasepostgresql
Parsa_Gholipour
Parsa_Gholipour

Reputation: 960

trying to select random data PostgreSQL

I'm trying to select random strings. The problem is it returns the same value for each row. Why is that and how to fix?

SELECT array_to_string(ARRAY(SELECT chr((48 + round(random() * 59)) :: integer) 
FROM generate_series(1,40)), '') AS string FROM generate_series(1,10);

Output:
|  string   |
| duplicate |
| duplicate |
| duplicate |
...

10 rows

Upvotes: 0

Views: 25

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

Postgres overoptimizes the subquery. I think this is an error, because it is missing the fact that random() is volatile.

A simple fix is a correlation clause:

select (select string_agg( chr(48 + (random() * 59)::int), '')
        from generate_series(1 ,40)
        where gs.i is not null
       ) AS string
from generate_series(1, 10) gs(i);

I rewrote the logic a bit, so it is simpler. There is no need to use arrays for what you want to do.

Here is a db<>fiddle.

Upvotes: 1

Related Questions