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