postgres random using setseed

Question

I would like to add a column with a random number using setseed to a table.

The original table structure (test_input) col_a,col_b,col_c

Desired output (test_output) col_a, col_b, col_c, random_id

The following returns the same random_id on all rows instead of a different value in each row.

select col_a,col_b,col_c,setseed(0.5),(
     select random() from generate_series(1,100) limit 1
     ) as random_id 
from test_input

Could you help me modify the query that uses setseed and returns a different random_id in each row?

Answer 1

You have to use setseed differently. Also generate_series() is misued in your example. You need to use something like:

select setseed(0.5);

select col_a,col_b,col_c, random() as random_id from test_input;

If you want to get the same random number assigned to the same row, you will have to sort rows first, quoting documentation:

If the ORDER BY clause is specified, the returned rows are sorted in the specified order. If ORDER BY is not given, the rows are returned in whatever order the system finds fastest to produce.

You can use:

select setseed(0.5);

select *, random() as random_id from (        
select col_a,col_b,col_c from test_input order by col_a, col_b, col_c) a;

Here I assume that combination of col_a, col_b, col_c is unique. If it's not the case, you will have to first add another column with unique ID to the table and sort by this column in the query above.