Obtain different random() values with generate_series() in PostgreSQL

Question

I am trying to generate random data (in volume) and was trying to use PostgreSQL's random() function with generate_series(), as shown:

SELECT
  (SELECT random()) AS a,
  (SELECT random()) AS b,
FROM generate_series(1,3);

However, I get the same random values per row:

   a   |   b   
-------+-------
 0.124 | 0.443 
 0.124 | 0.443 
 0.124 | 0.443 

How can this be solved?

Answer 1

Yes, quite an annoying "feature". You can fix the problem by stirring in a little of the outer query, in a way that doesn't change the result meaningfully.

SELECT
  (SELECT random()+f/1e39) AS a,
  (SELECT random()+f/1e39) AS b
FROM generate_series(1,3) f(f);

Answer 2

You can achieve that by calling the random() function separated from the generated_series() call like this:

    SELECT
    num,
    random() AS rand1,
    random() AS rand2
    FROM generate_series(1,10) AS gs(num)

But if you want to do some operations on the random columns, you will have to repeat the random() function call over and over as you can't call rand1 or rand2 directly.

A quick way to fix this is to use a CTE like this:

WITH rand AS (
    SELECT
    num,
    random() AS rand1,
    random() AS rand2
    FROM generate_series(1,10) AS s(num)
)
SELECT *
FROM rand
;