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?

Solution

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.

Comments

[user3416355]

Thanks, I gave this a try. However, if you run this query multiple times with the same setseed(0.5) the random_id changes. Do you know how to prevent this so I am able to replicate the sample?

[Kolyunya]

Having an application serving concurent requests from multiple users when each of them tries to set his personal seed and perform a select basing on that seed must both select queries be run in a transaction? What should be the isolation level? Can we combine two selects in one instead?

[Tomas Greif]

@Kolyunya I have no idea. Maybe it would be better to create new question and refer to this.