1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
|
--
-- NUMEROLOGY
-- Test various combinations of numeric types and functions.
--
--
-- Trailing junk in numeric literals
--
SELECT 123abc;
ERROR: trailing junk after numeric literal at or near "123a"
LINE 1: SELECT 123abc;
^
SELECT 0x0o;
ERROR: trailing junk after numeric literal at or near "0x"
LINE 1: SELECT 0x0o;
^
SELECT 1_2_3;
ERROR: trailing junk after numeric literal at or near "1_"
LINE 1: SELECT 1_2_3;
^
SELECT 0.a;
ERROR: trailing junk after numeric literal at or near "0.a"
LINE 1: SELECT 0.a;
^
SELECT 0.0a;
ERROR: trailing junk after numeric literal at or near "0.0a"
LINE 1: SELECT 0.0a;
^
SELECT .0a;
ERROR: trailing junk after numeric literal at or near ".0a"
LINE 1: SELECT .0a;
^
SELECT 0.0e1a;
ERROR: trailing junk after numeric literal at or near "0.0e1a"
LINE 1: SELECT 0.0e1a;
^
SELECT 0.0e;
ERROR: trailing junk after numeric literal at or near "0.0e"
LINE 1: SELECT 0.0e;
^
SELECT 0.0e+a;
ERROR: trailing junk after numeric literal at or near "0.0e+"
LINE 1: SELECT 0.0e+a;
^
PREPARE p1 AS SELECT $1a;
ERROR: trailing junk after parameter at or near "$1a"
LINE 1: PREPARE p1 AS SELECT $1a;
^
--
-- Test implicit type conversions
-- This fails for Postgres v6.1 (and earlier?)
-- so let's try explicit conversions for now - tgl 97/05/07
--
CREATE TABLE TEMP_FLOAT (f1 FLOAT8);
INSERT INTO TEMP_FLOAT (f1)
SELECT float8(f1) FROM INT4_TBL;
INSERT INTO TEMP_FLOAT (f1)
SELECT float8(f1) FROM INT2_TBL;
SELECT f1 FROM TEMP_FLOAT
ORDER BY f1;
f1
-------------
-2147483647
-123456
-32767
-1234
0
0
1234
32767
123456
2147483647
(10 rows)
-- int4
CREATE TABLE TEMP_INT4 (f1 INT4);
INSERT INTO TEMP_INT4 (f1)
SELECT int4(f1) FROM FLOAT8_TBL
WHERE (f1 > -2147483647) AND (f1 < 2147483647);
INSERT INTO TEMP_INT4 (f1)
SELECT int4(f1) FROM INT2_TBL;
SELECT f1 FROM TEMP_INT4
ORDER BY f1;
f1
--------
-32767
-1234
-1004
-35
0
0
0
1234
32767
(9 rows)
-- int2
CREATE TABLE TEMP_INT2 (f1 INT2);
INSERT INTO TEMP_INT2 (f1)
SELECT int2(f1) FROM FLOAT8_TBL
WHERE (f1 >= -32767) AND (f1 <= 32767);
INSERT INTO TEMP_INT2 (f1)
SELECT int2(f1) FROM INT4_TBL
WHERE (f1 >= -32767) AND (f1 <= 32767);
SELECT f1 FROM TEMP_INT2
ORDER BY f1;
f1
-------
-1004
-35
0
0
0
(5 rows)
--
-- Group-by combinations
--
CREATE TABLE TEMP_GROUP (f1 INT4, f2 INT4, f3 FLOAT8);
INSERT INTO TEMP_GROUP
SELECT 1, (- i.f1), (- f.f1)
FROM INT4_TBL i, FLOAT8_TBL f;
INSERT INTO TEMP_GROUP
SELECT 2, i.f1, f.f1
FROM INT4_TBL i, FLOAT8_TBL f;
SELECT DISTINCT f1 AS two FROM TEMP_GROUP ORDER BY 1;
two
-----
1
2
(2 rows)
SELECT f1 AS two, max(f3) AS max_float, min(f3) as min_float
FROM TEMP_GROUP
GROUP BY f1
ORDER BY two, max_float, min_float;
two | max_float | min_float
-----+----------------------+-----------------------
1 | 1.2345678901234e+200 | -0
2 | 0 | -1.2345678901234e+200
(2 rows)
-- GROUP BY a result column name is not legal per SQL92, but we accept it
-- anyway (if the name is not the name of any column exposed by FROM).
SELECT f1 AS two, max(f3) AS max_float, min(f3) AS min_float
FROM TEMP_GROUP
GROUP BY two
ORDER BY two, max_float, min_float;
two | max_float | min_float
-----+----------------------+-----------------------
1 | 1.2345678901234e+200 | -0
2 | 0 | -1.2345678901234e+200
(2 rows)
SELECT f1 AS two, (max(f3) + 1) AS max_plus_1, (min(f3) - 1) AS min_minus_1
FROM TEMP_GROUP
GROUP BY f1
ORDER BY two, min_minus_1;
two | max_plus_1 | min_minus_1
-----+----------------------+-----------------------
1 | 1.2345678901234e+200 | -1
2 | 1 | -1.2345678901234e+200
(2 rows)
SELECT f1 AS two,
max(f2) + min(f2) AS max_plus_min,
min(f3) - 1 AS min_minus_1
FROM TEMP_GROUP
GROUP BY f1
ORDER BY two, min_minus_1;
two | max_plus_min | min_minus_1
-----+--------------+-----------------------
1 | 0 | -1
2 | 0 | -1.2345678901234e+200
(2 rows)
DROP TABLE TEMP_INT2;
DROP TABLE TEMP_INT4;
DROP TABLE TEMP_FLOAT;
DROP TABLE TEMP_GROUP;
|
