1--
2-- RANDOM
3-- Test the random function
4--
5-- count the number of tuples originally, should be 1000
6SELECT count(*) FROM onek;
7 count
8-------
9  1000
10(1 row)
11
12-- pick three random rows, they shouldn't match
13(SELECT unique1 AS random
14  FROM onek ORDER BY random() LIMIT 1)
15INTERSECT
16(SELECT unique1 AS random
17  FROM onek ORDER BY random() LIMIT 1)
18INTERSECT
19(SELECT unique1 AS random
20  FROM onek ORDER BY random() LIMIT 1);
21 random
22--------
23(0 rows)
24
25-- count roughly 1/10 of the tuples
26SELECT count(*) AS random INTO RANDOM_TBL
27  FROM onek WHERE random() < 1.0/10;
28-- select again, the count should be different
29INSERT INTO RANDOM_TBL (random)
30  SELECT count(*)
31  FROM onek WHERE random() < 1.0/10;
32-- select again, the count should be different
33INSERT INTO RANDOM_TBL (random)
34  SELECT count(*)
35  FROM onek WHERE random() < 1.0/10;
36-- select again, the count should be different
37INSERT INTO RANDOM_TBL (random)
38  SELECT count(*)
39  FROM onek WHERE random() < 1.0/10;
40-- now test that they are different counts
41SELECT random, count(random) FROM RANDOM_TBL
42  GROUP BY random HAVING count(random) > 3;
43 random | count
44--------+-------
45(0 rows)
46
47SELECT AVG(random) FROM RANDOM_TBL
48  HAVING AVG(random) NOT BETWEEN 80 AND 120;
49 avg
50-----
51(0 rows)
52
53