trying to select random data PostgreSQL

Question

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

Gordon Linoff · Accepted Answer · 2020-03-26 16:40:08Z

1

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.

answered Mar 26, 2020 at 16:40

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

trying to select random data PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related