3
-- Test int8 64-bit integers.
5
CREATE TABLE INT8_TBL(q1 int8, q2 int8);
6
INSERT INTO INT8_TBL VALUES(' 123 ',' 456');
7
INSERT INTO INT8_TBL VALUES('123 ','4567890123456789');
8
INSERT INTO INT8_TBL VALUES('4567890123456789','123');
9
INSERT INTO INT8_TBL VALUES('4567890123456789','4567890123456789');
10
INSERT INTO INT8_TBL VALUES('4567890123456789','-4567890123456789');
12
INSERT INTO INT8_TBL(q1) VALUES (' ');
13
ERROR: invalid input syntax for integer: " "
14
INSERT INTO INT8_TBL(q1) VALUES ('xxx');
15
ERROR: invalid input syntax for integer: "xxx"
16
INSERT INTO INT8_TBL(q1) VALUES ('3908203590239580293850293850329485');
17
ERROR: value "3908203590239580293850293850329485" is out of range for type bigint
18
INSERT INTO INT8_TBL(q1) VALUES ('-1204982019841029840928340329840934');
19
ERROR: value "-1204982019841029840928340329840934" is out of range for type bigint
20
INSERT INTO INT8_TBL(q1) VALUES ('- 123');
21
ERROR: invalid input syntax for integer: "- 123"
22
INSERT INTO INT8_TBL(q1) VALUES (' 345 5');
23
ERROR: invalid input syntax for integer: " 345 5"
24
INSERT INTO INT8_TBL(q1) VALUES ('');
25
ERROR: invalid input syntax for integer: ""
26
SELECT * FROM INT8_TBL;
28
------------------+-------------------
30
123 | 4567890123456789
31
4567890123456789 | 123
32
4567890123456789 | 4567890123456789
33
4567890123456789 | -4567890123456789
36
SELECT '' AS five, q1 AS plus, -q1 AS minus FROM INT8_TBL;
38
------+------------------+-------------------
41
| 4567890123456789 | -4567890123456789
42
| 4567890123456789 | -4567890123456789
43
| 4567890123456789 | -4567890123456789
46
SELECT '' AS five, q1, q2, q1 + q2 AS plus FROM INT8_TBL;
48
------+------------------+-------------------+------------------
50
| 123 | 4567890123456789 | 4567890123456912
51
| 4567890123456789 | 123 | 4567890123456912
52
| 4567890123456789 | 4567890123456789 | 9135780246913578
53
| 4567890123456789 | -4567890123456789 | 0
56
SELECT '' AS five, q1, q2, q1 - q2 AS minus FROM INT8_TBL;
57
five | q1 | q2 | minus
58
------+------------------+-------------------+-------------------
60
| 123 | 4567890123456789 | -4567890123456666
61
| 4567890123456789 | 123 | 4567890123456666
62
| 4567890123456789 | 4567890123456789 | 0
63
| 4567890123456789 | -4567890123456789 | 9135780246913578
66
SELECT '' AS three, q1, q2, q1 * q2 AS multiply FROM INT8_TBL;
67
ERROR: bigint out of range
68
SELECT '' AS three, q1, q2, q1 * q2 AS multiply FROM INT8_TBL
69
WHERE q1 < 1000 or (q2 > 0 and q2 < 1000);
70
three | q1 | q2 | multiply
71
-------+------------------+------------------+--------------------
73
| 123 | 4567890123456789 | 561850485185185047
74
| 4567890123456789 | 123 | 561850485185185047
77
SELECT '' AS five, q1, q2, q1 / q2 AS divide FROM INT8_TBL;
78
five | q1 | q2 | divide
79
------+------------------+-------------------+----------------
81
| 123 | 4567890123456789 | 0
82
| 4567890123456789 | 123 | 37137318076884
83
| 4567890123456789 | 4567890123456789 | 1
84
| 4567890123456789 | -4567890123456789 | -1
87
SELECT '' AS five, q1, float8(q1) FROM INT8_TBL;
89
------+------------------+----------------------
92
| 4567890123456789 | 4.56789012345679e+15
93
| 4567890123456789 | 4.56789012345679e+15
94
| 4567890123456789 | 4.56789012345679e+15
97
SELECT '' AS five, q2, float8(q2) FROM INT8_TBL;
99
------+-------------------+-----------------------
101
| 4567890123456789 | 4.56789012345679e+15
103
| 4567890123456789 | 4.56789012345679e+15
104
| -4567890123456789 | -4.56789012345679e+15
107
SELECT '' AS five, 2 * q1 AS "twice int4" FROM INT8_TBL;
109
------+------------------
117
SELECT '' AS five, q1 * 2 AS "twice int4" FROM INT8_TBL;
119
------+------------------
129
SELECT '' AS to_char_1, to_char(q1, '9G999G999G999G999G999'), to_char(q2, '9,999,999,999,999,999')
131
to_char_1 | to_char | to_char
132
-----------+------------------------+------------------------
134
| 123 | 4,567,890,123,456,789
135
| 4,567,890,123,456,789 | 123
136
| 4,567,890,123,456,789 | 4,567,890,123,456,789
137
| 4,567,890,123,456,789 | -4,567,890,123,456,789
140
SELECT '' AS to_char_2, to_char(q1, '9G999G999G999G999G999D999G999'), to_char(q2, '9,999,999,999,999,999.999,999')
142
to_char_2 | to_char | to_char
143
-----------+--------------------------------+--------------------------------
144
| 123.000,000 | 456.000,000
145
| 123.000,000 | 4,567,890,123,456,789.000,000
146
| 4,567,890,123,456,789.000,000 | 123.000,000
147
| 4,567,890,123,456,789.000,000 | 4,567,890,123,456,789.000,000
148
| 4,567,890,123,456,789.000,000 | -4,567,890,123,456,789.000,000
151
SELECT '' AS to_char_3, to_char( (q1 * -1), '9999999999999999PR'), to_char( (q2 * -1), '9999999999999999.999PR')
153
to_char_3 | to_char | to_char
154
-----------+--------------------+------------------------
156
| <123> | <4567890123456789.000>
157
| <4567890123456789> | <123.000>
158
| <4567890123456789> | <4567890123456789.000>
159
| <4567890123456789> | 4567890123456789.000
162
SELECT '' AS to_char_4, to_char( (q1 * -1), '9999999999999999S'), to_char( (q2 * -1), 'S9999999999999999')
164
to_char_4 | to_char | to_char
165
-----------+-------------------+-------------------
167
| 123- | -4567890123456789
168
| 4567890123456789- | -123
169
| 4567890123456789- | -4567890123456789
170
| 4567890123456789- | +4567890123456789
173
SELECT '' AS to_char_5, to_char(q2, 'MI9999999999999999') FROM INT8_TBL;
175
-----------+-------------------
183
SELECT '' AS to_char_6, to_char(q2, 'FMS9999999999999999') FROM INT8_TBL;
185
-----------+-------------------
193
SELECT '' AS to_char_7, to_char(q2, 'FM9999999999999999THPR') FROM INT8_TBL;
195
-----------+--------------------
203
SELECT '' AS to_char_8, to_char(q2, 'SG9999999999999999th') FROM INT8_TBL;
205
-----------+---------------------
207
| +4567890123456789th
209
| +4567890123456789th
213
SELECT '' AS to_char_9, to_char(q2, '0999999999999999') FROM INT8_TBL;
215
-----------+-------------------
223
SELECT '' AS to_char_10, to_char(q2, 'S0999999999999999') FROM INT8_TBL;
225
------------+-------------------
233
SELECT '' AS to_char_11, to_char(q2, 'FM0999999999999999') FROM INT8_TBL;
235
------------+-------------------
243
SELECT '' AS to_char_12, to_char(q2, 'FM9999999999999999.000') FROM INT8_TBL;
245
------------+-----------------------
247
| 4567890123456789.000
249
| 4567890123456789.000
250
| -4567890123456789.000
253
SELECT '' AS to_char_13, to_char(q2, 'L9999999999999999.000') FROM INT8_TBL;
255
------------+------------------------
257
| 4567890123456789.000
259
| 4567890123456789.000
260
| -4567890123456789.000
263
SELECT '' AS to_char_14, to_char(q2, 'FM9999999999999999.999') FROM INT8_TBL;
265
------------+--------------------
273
SELECT '' AS to_char_15, to_char(q2, 'S 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 9 . 9 9 9') FROM INT8_TBL;
275
------------+-------------------------------------------
277
| +4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 . 0 0 0
279
| +4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 . 0 0 0
280
| -4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 . 0 0 0
283
SELECT '' AS to_char_16, to_char(q2, '99999 "text" 9999 "9999" 999 "\\"text between quote marks\\"" 9999') FROM INT8_TBL;
285
------------+-----------------------------------------------------------
286
| text 9999 "text between quote marks" 456
287
| 45678 text 9012 9999 345 "text between quote marks" 6789
288
| text 9999 "text between quote marks" 123
289
| 45678 text 9012 9999 345 "text between quote marks" 6789
290
| -45678 text 9012 9999 345 "text between quote marks" 6789
293
SELECT '' AS to_char_17, to_char(q2, '999999SG9999999999') FROM INT8_TBL;
295
------------+-------------------