1--
2-- RANDOM
3-- Test the random function
4--
5
6-- count the number of tuples originally, should be 1000
7SELECT count(*) FROM onek;
8
9-- pick three random rows, they shouldn't match
10(SELECT unique1 AS random
11  FROM onek ORDER BY random() LIMIT 1)
12INTERSECT
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);
18
19-- count roughly 1/10 of the tuples
20SELECT count(*) AS random INTO RANDOM_TBL
21  FROM onek WHERE random() < 1.0/10;
22
23-- select again, the count should be different
24INSERT INTO RANDOM_TBL (random)
25  SELECT count(*)
26  FROM onek WHERE random() < 1.0/10;
27
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
33-- select again, the count should be different
34INSERT INTO RANDOM_TBL (random)
35  SELECT count(*)
36  FROM onek WHERE random() < 1.0/10;
37
38-- now test that they are different counts
39SELECT random, count(random) FROM RANDOM_TBL
40  GROUP BY random HAVING count(random) > 3;
41
42SELECT AVG(random) FROM RANDOM_TBL
43  HAVING AVG(random) NOT BETWEEN 80 AND 120;
44