1--
2-- NUMEROLOGY
3-- Test various combinations of numeric types and functions.
4--
5
6--
7-- Test implicit type conversions
8-- This fails for Postgres v6.1 (and earlier?)
9--  so let's try explicit conversions for now - tgl 97/05/07
10--
11
12CREATE TABLE TEMP_FLOAT (f1 FLOAT8);
13
14INSERT INTO TEMP_FLOAT (f1)
15  SELECT float8(f1) FROM INT4_TBL;
16
17INSERT INTO TEMP_FLOAT (f1)
18  SELECT float8(f1) FROM INT2_TBL;
19
20SELECT '' AS ten, f1 FROM TEMP_FLOAT
21  ORDER BY f1;
22
23-- int4
24
25CREATE TABLE TEMP_INT4 (f1 INT4);
26
27INSERT INTO TEMP_INT4 (f1)
28  SELECT int4(f1) FROM FLOAT8_TBL
29  WHERE (f1 > -2147483647) AND (f1 < 2147483647);
30
31INSERT INTO TEMP_INT4 (f1)
32  SELECT int4(f1) FROM INT2_TBL;
33
34SELECT '' AS nine, f1 FROM TEMP_INT4
35  ORDER BY f1;
36
37-- int2
38
39CREATE TABLE TEMP_INT2 (f1 INT2);
40
41INSERT INTO TEMP_INT2 (f1)
42  SELECT int2(f1) FROM FLOAT8_TBL
43  WHERE (f1 >= -32767) AND (f1 <= 32767);
44
45INSERT INTO TEMP_INT2 (f1)
46  SELECT int2(f1) FROM INT4_TBL
47  WHERE (f1 >= -32767) AND (f1 <= 32767);
48
49SELECT '' AS five, f1 FROM TEMP_INT2
50  ORDER BY f1;
51
52--
53-- Group-by combinations
54--
55
56CREATE TABLE TEMP_GROUP (f1 INT4, f2 INT4, f3 FLOAT8);
57
58INSERT INTO TEMP_GROUP
59  SELECT 1, (- i.f1), (- f.f1)
60  FROM INT4_TBL i, FLOAT8_TBL f;
61
62INSERT INTO TEMP_GROUP
63  SELECT 2, i.f1, f.f1
64  FROM INT4_TBL i, FLOAT8_TBL f;
65
66SELECT DISTINCT f1 AS two FROM TEMP_GROUP ORDER BY 1;
67
68SELECT f1 AS two, max(f3) AS max_float, min(f3) as min_float
69  FROM TEMP_GROUP
70  GROUP BY f1
71  ORDER BY two, max_float, min_float;
72
73-- GROUP BY a result column name is not legal per SQL92, but we accept it
74-- anyway (if the name is not the name of any column exposed by FROM).
75SELECT f1 AS two, max(f3) AS max_float, min(f3) AS min_float
76  FROM TEMP_GROUP
77  GROUP BY two
78  ORDER BY two, max_float, min_float;
79
80SELECT f1 AS two, (max(f3) + 1) AS max_plus_1, (min(f3) - 1) AS min_minus_1
81  FROM TEMP_GROUP
82  GROUP BY f1
83  ORDER BY two, min_minus_1;
84
85SELECT f1 AS two,
86       max(f2) + min(f2) AS max_plus_min,
87       min(f3) - 1 AS min_minus_1
88  FROM TEMP_GROUP
89  GROUP BY f1
90  ORDER BY two, min_minus_1;
91
92DROP TABLE TEMP_INT2;
93
94DROP TABLE TEMP_INT4;
95
96DROP TABLE TEMP_FLOAT;
97
98DROP TABLE TEMP_GROUP;
99