postgres random using setseed

postgresql random sample random-seed
user3416355 picture user3416355 · Mar 13, 2014 · Viewed 9k times · Source

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

Tomas Greif picture Tomas Greif · Mar 13, 2014

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.

Related questions

Best way to select random rows PostgreSQL

I want a random selection of rows in PostgreSQL, I tried this: select * from table where random() < 0.01; But some other recommend this: select * from table order by random() limit 1000; I have a very large table with 500 Million rows, I …

Read More
Postgres FOR LOOP

I am trying to get 25 random samples of 15,000 IDs from a table. Instead of manually pressing run every time, I'm trying to do a loop. Which I fully understand is not the optimum use of Postgres, but it is the …

Read More
Generate a random number in the range 1 - 10

Since my approach for a test query which I worked on in this question did not work out, I'm trying something else now. Is there a way to tell pg's random() function to get me only numbers between 1 and 10?

Read More