Or perhaps: CREATE OR REPLACE VIEW test_view AS SELECT (random()*3)::int as test_value; At least in this case, that should give the same result. in this case 1/3 should be 1, 1/3 = 2 & 1/3=3 in your case 1/3 = 1, 1/2 the remainder (1/2 * 2/3 = 1/3) = 2, remaining 1/3 = 3 Although I'm guessing the original intent is to NOT generate an equal distribution, but I'm not sure what distribution is required. Cheers, Brent Wood Brent Wood DBA/GIS consultant NIWA, Wellington New Zealand >>> ries van Twisk <pg@xxxxxxxxxx> 02/24/09 12:13 PM >>> Jessi, should the function not look like this??? CREATE OR REPLACE VIEW test_view AS SELECT CASE WHEN random() < .333333333 THEN '1' WHEN random() < .5 THEN '2' ELSE '3' END AS test_value FROM client; On Feb 23, 2009, at 5:09 PM, Jessi Berkelhammer wrote: > Hi, > > I have a view in which I want to randomly assign values if certain > conditions hold. I was getting surprising results. Here is a (very) > simplified version of the view, which seems to indicate the problem: > > CREATE OR REPLACE VIEW test_view AS > SELECT > CASE > WHEN random() < .3333 THEN '1' > WHEN random() < .3333 THEN '2' > ELSE '3' > END AS test_value > > FROM client ; > > It seems this should generate a random number between 0 and 1, and set > test_value to '1' if this first generated number is less than .3333. > Otherwise, it should generate another random number, and set > test_value > to '2' if this is less than .3333. And if neither of the random > numbers > are less than .3333, it should set test_value to '3'. It seems to me > that there should be a relative even distribution of the 3 values. > > However when I run this, the values are always similar to what is > below: > > X_test=> select test_value, count(*) from test_view group by 1 > order by 1; > test_value | count > ------------+------- > 1 | 23947 > 2 | 16061 > 3 | 32443 > > Why are there significantly fewer 2s? I understand that random() is > not > truly random, and that the seed affects this value. But it still > confuses me that, no matter how many times I run this, there are > always > so few 2s. If it is generating an independent random number in the > second call to random(), then I don't know why there are more so many > more 1s than 2s. > > Thanks! > -jessi > > -- > Jessi Berkelhammer > Downtown Emergency Service Center > Computer Programming Specialist -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general NIWA is the trading name of the National Institute of Water & Atmospheric Research Ltd. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
