summaryrefslogtreecommitdiffstats
path: root/src/test/regress/expected/int8.out
diff options
context:
space:
mode:
Diffstat (limited to 'src/test/regress/expected/int8.out')
-rw-r--r--src/test/regress/expected/int8.out934
1 files changed, 934 insertions, 0 deletions
diff --git a/src/test/regress/expected/int8.out b/src/test/regress/expected/int8.out
new file mode 100644
index 0000000..813e3a8
--- /dev/null
+++ b/src/test/regress/expected/int8.out
@@ -0,0 +1,934 @@
+--
+-- INT8
+-- Test int8 64-bit integers.
+--
+CREATE TABLE INT8_TBL(q1 int8, q2 int8);
+INSERT INTO INT8_TBL VALUES(' 123 ',' 456');
+INSERT INTO INT8_TBL VALUES('123 ','4567890123456789');
+INSERT INTO INT8_TBL VALUES('4567890123456789','123');
+INSERT INTO INT8_TBL VALUES(+4567890123456789,'4567890123456789');
+INSERT INTO INT8_TBL VALUES('+4567890123456789','-4567890123456789');
+-- bad inputs
+INSERT INTO INT8_TBL(q1) VALUES (' ');
+ERROR: invalid input syntax for type bigint: " "
+LINE 1: INSERT INTO INT8_TBL(q1) VALUES (' ');
+ ^
+INSERT INTO INT8_TBL(q1) VALUES ('xxx');
+ERROR: invalid input syntax for type bigint: "xxx"
+LINE 1: INSERT INTO INT8_TBL(q1) VALUES ('xxx');
+ ^
+INSERT INTO INT8_TBL(q1) VALUES ('3908203590239580293850293850329485');
+ERROR: value "3908203590239580293850293850329485" is out of range for type bigint
+LINE 1: INSERT INTO INT8_TBL(q1) VALUES ('39082035902395802938502938...
+ ^
+INSERT INTO INT8_TBL(q1) VALUES ('-1204982019841029840928340329840934');
+ERROR: value "-1204982019841029840928340329840934" is out of range for type bigint
+LINE 1: INSERT INTO INT8_TBL(q1) VALUES ('-1204982019841029840928340...
+ ^
+INSERT INTO INT8_TBL(q1) VALUES ('- 123');
+ERROR: invalid input syntax for type bigint: "- 123"
+LINE 1: INSERT INTO INT8_TBL(q1) VALUES ('- 123');
+ ^
+INSERT INTO INT8_TBL(q1) VALUES (' 345 5');
+ERROR: invalid input syntax for type bigint: " 345 5"
+LINE 1: INSERT INTO INT8_TBL(q1) VALUES (' 345 5');
+ ^
+INSERT INTO INT8_TBL(q1) VALUES ('');
+ERROR: invalid input syntax for type bigint: ""
+LINE 1: INSERT INTO INT8_TBL(q1) VALUES ('');
+ ^
+SELECT * FROM INT8_TBL;
+ q1 | q2
+------------------+-------------------
+ 123 | 456
+ 123 | 4567890123456789
+ 4567890123456789 | 123
+ 4567890123456789 | 4567890123456789
+ 4567890123456789 | -4567890123456789
+(5 rows)
+
+-- int8/int8 cmp
+SELECT * FROM INT8_TBL WHERE q2 = 4567890123456789;
+ q1 | q2
+------------------+------------------
+ 123 | 4567890123456789
+ 4567890123456789 | 4567890123456789
+(2 rows)
+
+SELECT * FROM INT8_TBL WHERE q2 <> 4567890123456789;
+ q1 | q2
+------------------+-------------------
+ 123 | 456
+ 4567890123456789 | 123
+ 4567890123456789 | -4567890123456789
+(3 rows)
+
+SELECT * FROM INT8_TBL WHERE q2 < 4567890123456789;
+ q1 | q2
+------------------+-------------------
+ 123 | 456
+ 4567890123456789 | 123
+ 4567890123456789 | -4567890123456789
+(3 rows)
+
+SELECT * FROM INT8_TBL WHERE q2 > 4567890123456789;
+ q1 | q2
+----+----
+(0 rows)
+
+SELECT * FROM INT8_TBL WHERE q2 <= 4567890123456789;
+ q1 | q2
+------------------+-------------------
+ 123 | 456
+ 123 | 4567890123456789
+ 4567890123456789 | 123
+ 4567890123456789 | 4567890123456789
+ 4567890123456789 | -4567890123456789
+(5 rows)
+
+SELECT * FROM INT8_TBL WHERE q2 >= 4567890123456789;
+ q1 | q2
+------------------+------------------
+ 123 | 4567890123456789
+ 4567890123456789 | 4567890123456789
+(2 rows)
+
+-- int8/int4 cmp
+SELECT * FROM INT8_TBL WHERE q2 = 456;
+ q1 | q2
+-----+-----
+ 123 | 456
+(1 row)
+
+SELECT * FROM INT8_TBL WHERE q2 <> 456;
+ q1 | q2
+------------------+-------------------
+ 123 | 4567890123456789
+ 4567890123456789 | 123
+ 4567890123456789 | 4567890123456789
+ 4567890123456789 | -4567890123456789
+(4 rows)
+
+SELECT * FROM INT8_TBL WHERE q2 < 456;
+ q1 | q2
+------------------+-------------------
+ 4567890123456789 | 123
+ 4567890123456789 | -4567890123456789
+(2 rows)
+
+SELECT * FROM INT8_TBL WHERE q2 > 456;
+ q1 | q2
+------------------+------------------
+ 123 | 4567890123456789
+ 4567890123456789 | 4567890123456789
+(2 rows)
+
+SELECT * FROM INT8_TBL WHERE q2 <= 456;
+ q1 | q2
+------------------+-------------------
+ 123 | 456
+ 4567890123456789 | 123
+ 4567890123456789 | -4567890123456789
+(3 rows)
+
+SELECT * FROM INT8_TBL WHERE q2 >= 456;
+ q1 | q2
+------------------+------------------
+ 123 | 456
+ 123 | 4567890123456789
+ 4567890123456789 | 4567890123456789
+(3 rows)
+
+-- int4/int8 cmp
+SELECT * FROM INT8_TBL WHERE 123 = q1;
+ q1 | q2
+-----+------------------
+ 123 | 456
+ 123 | 4567890123456789
+(2 rows)
+
+SELECT * FROM INT8_TBL WHERE 123 <> q1;
+ q1 | q2
+------------------+-------------------
+ 4567890123456789 | 123
+ 4567890123456789 | 4567890123456789
+ 4567890123456789 | -4567890123456789
+(3 rows)
+
+SELECT * FROM INT8_TBL WHERE 123 < q1;
+ q1 | q2
+------------------+-------------------
+ 4567890123456789 | 123
+ 4567890123456789 | 4567890123456789
+ 4567890123456789 | -4567890123456789
+(3 rows)
+
+SELECT * FROM INT8_TBL WHERE 123 > q1;
+ q1 | q2
+----+----
+(0 rows)
+
+SELECT * FROM INT8_TBL WHERE 123 <= q1;
+ q1 | q2
+------------------+-------------------
+ 123 | 456
+ 123 | 4567890123456789
+ 4567890123456789 | 123
+ 4567890123456789 | 4567890123456789
+ 4567890123456789 | -4567890123456789
+(5 rows)
+
+SELECT * FROM INT8_TBL WHERE 123 >= q1;
+ q1 | q2
+-----+------------------
+ 123 | 456
+ 123 | 4567890123456789
+(2 rows)
+
+-- int8/int2 cmp
+SELECT * FROM INT8_TBL WHERE q2 = '456'::int2;
+ q1 | q2
+-----+-----
+ 123 | 456
+(1 row)
+
+SELECT * FROM INT8_TBL WHERE q2 <> '456'::int2;
+ q1 | q2
+------------------+-------------------
+ 123 | 4567890123456789
+ 4567890123456789 | 123
+ 4567890123456789 | 4567890123456789
+ 4567890123456789 | -4567890123456789
+(4 rows)
+
+SELECT * FROM INT8_TBL WHERE q2 < '456'::int2;
+ q1 | q2
+------------------+-------------------
+ 4567890123456789 | 123
+ 4567890123456789 | -4567890123456789
+(2 rows)
+
+SELECT * FROM INT8_TBL WHERE q2 > '456'::int2;
+ q1 | q2
+------------------+------------------
+ 123 | 4567890123456789
+ 4567890123456789 | 4567890123456789
+(2 rows)
+
+SELECT * FROM INT8_TBL WHERE q2 <= '456'::int2;
+ q1 | q2
+------------------+-------------------
+ 123 | 456
+ 4567890123456789 | 123
+ 4567890123456789 | -4567890123456789
+(3 rows)
+
+SELECT * FROM INT8_TBL WHERE q2 >= '456'::int2;
+ q1 | q2
+------------------+------------------
+ 123 | 456
+ 123 | 4567890123456789
+ 4567890123456789 | 4567890123456789
+(3 rows)
+
+-- int2/int8 cmp
+SELECT * FROM INT8_TBL WHERE '123'::int2 = q1;
+ q1 | q2
+-----+------------------
+ 123 | 456
+ 123 | 4567890123456789
+(2 rows)
+
+SELECT * FROM INT8_TBL WHERE '123'::int2 <> q1;
+ q1 | q2
+------------------+-------------------
+ 4567890123456789 | 123
+ 4567890123456789 | 4567890123456789
+ 4567890123456789 | -4567890123456789
+(3 rows)
+
+SELECT * FROM INT8_TBL WHERE '123'::int2 < q1;
+ q1 | q2
+------------------+-------------------
+ 4567890123456789 | 123
+ 4567890123456789 | 4567890123456789
+ 4567890123456789 | -4567890123456789
+(3 rows)
+
+SELECT * FROM INT8_TBL WHERE '123'::int2 > q1;
+ q1 | q2
+----+----
+(0 rows)
+
+SELECT * FROM INT8_TBL WHERE '123'::int2 <= q1;
+ q1 | q2
+------------------+-------------------
+ 123 | 456
+ 123 | 4567890123456789
+ 4567890123456789 | 123
+ 4567890123456789 | 4567890123456789
+ 4567890123456789 | -4567890123456789
+(5 rows)
+
+SELECT * FROM INT8_TBL WHERE '123'::int2 >= q1;
+ q1 | q2
+-----+------------------
+ 123 | 456
+ 123 | 4567890123456789
+(2 rows)
+
+SELECT '' AS five, q1 AS plus, -q1 AS minus FROM INT8_TBL;
+ five | plus | minus
+------+------------------+-------------------
+ | 123 | -123
+ | 123 | -123
+ | 4567890123456789 | -4567890123456789
+ | 4567890123456789 | -4567890123456789
+ | 4567890123456789 | -4567890123456789
+(5 rows)
+
+SELECT '' AS five, q1, q2, q1 + q2 AS plus FROM INT8_TBL;
+ five | q1 | q2 | plus
+------+------------------+-------------------+------------------
+ | 123 | 456 | 579
+ | 123 | 4567890123456789 | 4567890123456912
+ | 4567890123456789 | 123 | 4567890123456912
+ | 4567890123456789 | 4567890123456789 | 9135780246913578
+ | 4567890123456789 | -4567890123456789 | 0
+(5 rows)
+
+SELECT '' AS five, q1, q2, q1 - q2 AS minus FROM INT8_TBL;
+ five | q1 | q2 | minus
+------+------------------+-------------------+-------------------
+ | 123 | 456 | -333
+ | 123 | 4567890123456789 | -4567890123456666
+ | 4567890123456789 | 123 | 4567890123456666
+ | 4567890123456789 | 4567890123456789 | 0
+ | 4567890123456789 | -4567890123456789 | 9135780246913578
+(5 rows)
+
+SELECT '' AS three, q1, q2, q1 * q2 AS multiply FROM INT8_TBL;
+ERROR: bigint out of range
+SELECT '' AS three, q1, q2, q1 * q2 AS multiply FROM INT8_TBL
+ WHERE q1 < 1000 or (q2 > 0 and q2 < 1000);
+ three | q1 | q2 | multiply
+-------+------------------+------------------+--------------------
+ | 123 | 456 | 56088
+ | 123 | 4567890123456789 | 561850485185185047
+ | 4567890123456789 | 123 | 561850485185185047
+(3 rows)
+
+SELECT '' AS five, q1, q2, q1 / q2 AS divide, q1 % q2 AS mod FROM INT8_TBL;
+ five | q1 | q2 | divide | mod
+------+------------------+-------------------+----------------+-----
+ | 123 | 456 | 0 | 123
+ | 123 | 4567890123456789 | 0 | 123
+ | 4567890123456789 | 123 | 37137318076884 | 57
+ | 4567890123456789 | 4567890123456789 | 1 | 0
+ | 4567890123456789 | -4567890123456789 | -1 | 0
+(5 rows)
+
+SELECT '' AS five, q1, float8(q1) FROM INT8_TBL;
+ five | q1 | float8
+------+------------------+-----------------------
+ | 123 | 123
+ | 123 | 123
+ | 4567890123456789 | 4.567890123456789e+15
+ | 4567890123456789 | 4.567890123456789e+15
+ | 4567890123456789 | 4.567890123456789e+15
+(5 rows)
+
+SELECT '' AS five, q2, float8(q2) FROM INT8_TBL;
+ five | q2 | float8
+------+-------------------+------------------------
+ | 456 | 456
+ | 4567890123456789 | 4.567890123456789e+15
+ | 123 | 123
+ | 4567890123456789 | 4.567890123456789e+15
+ | -4567890123456789 | -4.567890123456789e+15
+(5 rows)
+
+SELECT 37 + q1 AS plus4 FROM INT8_TBL;
+ plus4
+------------------
+ 160
+ 160
+ 4567890123456826
+ 4567890123456826
+ 4567890123456826
+(5 rows)
+
+SELECT 37 - q1 AS minus4 FROM INT8_TBL;
+ minus4
+-------------------
+ -86
+ -86
+ -4567890123456752
+ -4567890123456752
+ -4567890123456752
+(5 rows)
+
+SELECT '' AS five, 2 * q1 AS "twice int4" FROM INT8_TBL;
+ five | twice int4
+------+------------------
+ | 246
+ | 246
+ | 9135780246913578
+ | 9135780246913578
+ | 9135780246913578
+(5 rows)
+
+SELECT '' AS five, q1 * 2 AS "twice int4" FROM INT8_TBL;
+ five | twice int4
+------+------------------
+ | 246
+ | 246
+ | 9135780246913578
+ | 9135780246913578
+ | 9135780246913578
+(5 rows)
+
+-- int8 op int4
+SELECT q1 + 42::int4 AS "8plus4", q1 - 42::int4 AS "8minus4", q1 * 42::int4 AS "8mul4", q1 / 42::int4 AS "8div4" FROM INT8_TBL;
+ 8plus4 | 8minus4 | 8mul4 | 8div4
+------------------+------------------+--------------------+-----------------
+ 165 | 81 | 5166 | 2
+ 165 | 81 | 5166 | 2
+ 4567890123456831 | 4567890123456747 | 191851385185185138 | 108759288653733
+ 4567890123456831 | 4567890123456747 | 191851385185185138 | 108759288653733
+ 4567890123456831 | 4567890123456747 | 191851385185185138 | 108759288653733
+(5 rows)
+
+-- int4 op int8
+SELECT 246::int4 + q1 AS "4plus8", 246::int4 - q1 AS "4minus8", 246::int4 * q1 AS "4mul8", 246::int4 / q1 AS "4div8" FROM INT8_TBL;
+ 4plus8 | 4minus8 | 4mul8 | 4div8
+------------------+-------------------+---------------------+-------
+ 369 | 123 | 30258 | 2
+ 369 | 123 | 30258 | 2
+ 4567890123457035 | -4567890123456543 | 1123700970370370094 | 0
+ 4567890123457035 | -4567890123456543 | 1123700970370370094 | 0
+ 4567890123457035 | -4567890123456543 | 1123700970370370094 | 0
+(5 rows)
+
+-- int8 op int2
+SELECT q1 + 42::int2 AS "8plus2", q1 - 42::int2 AS "8minus2", q1 * 42::int2 AS "8mul2", q1 / 42::int2 AS "8div2" FROM INT8_TBL;
+ 8plus2 | 8minus2 | 8mul2 | 8div2
+------------------+------------------+--------------------+-----------------
+ 165 | 81 | 5166 | 2
+ 165 | 81 | 5166 | 2
+ 4567890123456831 | 4567890123456747 | 191851385185185138 | 108759288653733
+ 4567890123456831 | 4567890123456747 | 191851385185185138 | 108759288653733
+ 4567890123456831 | 4567890123456747 | 191851385185185138 | 108759288653733
+(5 rows)
+
+-- int2 op int8
+SELECT 246::int2 + q1 AS "2plus8", 246::int2 - q1 AS "2minus8", 246::int2 * q1 AS "2mul8", 246::int2 / q1 AS "2div8" FROM INT8_TBL;
+ 2plus8 | 2minus8 | 2mul8 | 2div8
+------------------+-------------------+---------------------+-------
+ 369 | 123 | 30258 | 2
+ 369 | 123 | 30258 | 2
+ 4567890123457035 | -4567890123456543 | 1123700970370370094 | 0
+ 4567890123457035 | -4567890123456543 | 1123700970370370094 | 0
+ 4567890123457035 | -4567890123456543 | 1123700970370370094 | 0
+(5 rows)
+
+SELECT q2, abs(q2) FROM INT8_TBL;
+ q2 | abs
+-------------------+------------------
+ 456 | 456
+ 4567890123456789 | 4567890123456789
+ 123 | 123
+ 4567890123456789 | 4567890123456789
+ -4567890123456789 | 4567890123456789
+(5 rows)
+
+SELECT min(q1), min(q2) FROM INT8_TBL;
+ min | min
+-----+-------------------
+ 123 | -4567890123456789
+(1 row)
+
+SELECT max(q1), max(q2) FROM INT8_TBL;
+ max | max
+------------------+------------------
+ 4567890123456789 | 4567890123456789
+(1 row)
+
+-- TO_CHAR()
+--
+SELECT '' AS to_char_1, to_char(q1, '9G999G999G999G999G999'), to_char(q2, '9,999,999,999,999,999')
+ FROM INT8_TBL;
+ to_char_1 | to_char | to_char
+-----------+------------------------+------------------------
+ | 123 | 456
+ | 123 | 4,567,890,123,456,789
+ | 4,567,890,123,456,789 | 123
+ | 4,567,890,123,456,789 | 4,567,890,123,456,789
+ | 4,567,890,123,456,789 | -4,567,890,123,456,789
+(5 rows)
+
+SELECT '' AS to_char_2, to_char(q1, '9G999G999G999G999G999D999G999'), to_char(q2, '9,999,999,999,999,999.999,999')
+ FROM INT8_TBL;
+ to_char_2 | to_char | to_char
+-----------+--------------------------------+--------------------------------
+ | 123.000,000 | 456.000,000
+ | 123.000,000 | 4,567,890,123,456,789.000,000
+ | 4,567,890,123,456,789.000,000 | 123.000,000
+ | 4,567,890,123,456,789.000,000 | 4,567,890,123,456,789.000,000
+ | 4,567,890,123,456,789.000,000 | -4,567,890,123,456,789.000,000
+(5 rows)
+
+SELECT '' AS to_char_3, to_char( (q1 * -1), '9999999999999999PR'), to_char( (q2 * -1), '9999999999999999.999PR')
+ FROM INT8_TBL;
+ to_char_3 | to_char | to_char
+-----------+--------------------+------------------------
+ | <123> | <456.000>
+ | <123> | <4567890123456789.000>
+ | <4567890123456789> | <123.000>
+ | <4567890123456789> | <4567890123456789.000>
+ | <4567890123456789> | 4567890123456789.000
+(5 rows)
+
+SELECT '' AS to_char_4, to_char( (q1 * -1), '9999999999999999S'), to_char( (q2 * -1), 'S9999999999999999')
+ FROM INT8_TBL;
+ to_char_4 | to_char | to_char
+-----------+-------------------+-------------------
+ | 123- | -456
+ | 123- | -4567890123456789
+ | 4567890123456789- | -123
+ | 4567890123456789- | -4567890123456789
+ | 4567890123456789- | +4567890123456789
+(5 rows)
+
+SELECT '' AS to_char_5, to_char(q2, 'MI9999999999999999') FROM INT8_TBL;
+ to_char_5 | to_char
+-----------+-------------------
+ | 456
+ | 4567890123456789
+ | 123
+ | 4567890123456789
+ | -4567890123456789
+(5 rows)
+
+SELECT '' AS to_char_6, to_char(q2, 'FMS9999999999999999') FROM INT8_TBL;
+ to_char_6 | to_char
+-----------+-------------------
+ | +456
+ | +4567890123456789
+ | +123
+ | +4567890123456789
+ | -4567890123456789
+(5 rows)
+
+SELECT '' AS to_char_7, to_char(q2, 'FM9999999999999999THPR') FROM INT8_TBL;
+ to_char_7 | to_char
+-----------+--------------------
+ | 456TH
+ | 4567890123456789TH
+ | 123RD
+ | 4567890123456789TH
+ | <4567890123456789>
+(5 rows)
+
+SELECT '' AS to_char_8, to_char(q2, 'SG9999999999999999th') FROM INT8_TBL;
+ to_char_8 | to_char
+-----------+---------------------
+ | + 456th
+ | +4567890123456789th
+ | + 123rd
+ | +4567890123456789th
+ | -4567890123456789
+(5 rows)
+
+SELECT '' AS to_char_9, to_char(q2, '0999999999999999') FROM INT8_TBL;
+ to_char_9 | to_char
+-----------+-------------------
+ | 0000000000000456
+ | 4567890123456789
+ | 0000000000000123
+ | 4567890123456789
+ | -4567890123456789
+(5 rows)
+
+SELECT '' AS to_char_10, to_char(q2, 'S0999999999999999') FROM INT8_TBL;
+ to_char_10 | to_char
+------------+-------------------
+ | +0000000000000456
+ | +4567890123456789
+ | +0000000000000123
+ | +4567890123456789
+ | -4567890123456789
+(5 rows)
+
+SELECT '' AS to_char_11, to_char(q2, 'FM0999999999999999') FROM INT8_TBL;
+ to_char_11 | to_char
+------------+-------------------
+ | 0000000000000456
+ | 4567890123456789
+ | 0000000000000123
+ | 4567890123456789
+ | -4567890123456789
+(5 rows)
+
+SELECT '' AS to_char_12, to_char(q2, 'FM9999999999999999.000') FROM INT8_TBL;
+ to_char_12 | to_char
+------------+-----------------------
+ | 456.000
+ | 4567890123456789.000
+ | 123.000
+ | 4567890123456789.000
+ | -4567890123456789.000
+(5 rows)
+
+SELECT '' AS to_char_13, to_char(q2, 'L9999999999999999.000') FROM INT8_TBL;
+ to_char_13 | to_char
+------------+------------------------
+ | 456.000
+ | 4567890123456789.000
+ | 123.000
+ | 4567890123456789.000
+ | -4567890123456789.000
+(5 rows)
+
+SELECT '' AS to_char_14, to_char(q2, 'FM9999999999999999.999') FROM INT8_TBL;
+ to_char_14 | to_char
+------------+--------------------
+ | 456.
+ | 4567890123456789.
+ | 123.
+ | 4567890123456789.
+ | -4567890123456789.
+(5 rows)
+
+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;
+ to_char_15 | to_char
+------------+-------------------------------------------
+ | +4 5 6 . 0 0 0
+ | +4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 . 0 0 0
+ | +1 2 3 . 0 0 0
+ | +4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 . 0 0 0
+ | -4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 . 0 0 0
+(5 rows)
+
+SELECT '' AS to_char_16, to_char(q2, E'99999 "text" 9999 "9999" 999 "\\"text between quote marks\\"" 9999') FROM INT8_TBL;
+ to_char_16 | to_char
+------------+-----------------------------------------------------------
+ | text 9999 "text between quote marks" 456
+ | 45678 text 9012 9999 345 "text between quote marks" 6789
+ | text 9999 "text between quote marks" 123
+ | 45678 text 9012 9999 345 "text between quote marks" 6789
+ | -45678 text 9012 9999 345 "text between quote marks" 6789
+(5 rows)
+
+SELECT '' AS to_char_17, to_char(q2, '999999SG9999999999') FROM INT8_TBL;
+ to_char_17 | to_char
+------------+-------------------
+ | + 456
+ | 456789+0123456789
+ | + 123
+ | 456789+0123456789
+ | 456789-0123456789
+(5 rows)
+
+-- check min/max values and overflow behavior
+select '-9223372036854775808'::int8;
+ int8
+----------------------
+ -9223372036854775808
+(1 row)
+
+select '-9223372036854775809'::int8;
+ERROR: value "-9223372036854775809" is out of range for type bigint
+LINE 1: select '-9223372036854775809'::int8;
+ ^
+select '9223372036854775807'::int8;
+ int8
+---------------------
+ 9223372036854775807
+(1 row)
+
+select '9223372036854775808'::int8;
+ERROR: value "9223372036854775808" is out of range for type bigint
+LINE 1: select '9223372036854775808'::int8;
+ ^
+select -('-9223372036854775807'::int8);
+ ?column?
+---------------------
+ 9223372036854775807
+(1 row)
+
+select -('-9223372036854775808'::int8);
+ERROR: bigint out of range
+select '9223372036854775800'::int8 + '9223372036854775800'::int8;
+ERROR: bigint out of range
+select '-9223372036854775800'::int8 + '-9223372036854775800'::int8;
+ERROR: bigint out of range
+select '9223372036854775800'::int8 - '-9223372036854775800'::int8;
+ERROR: bigint out of range
+select '-9223372036854775800'::int8 - '9223372036854775800'::int8;
+ERROR: bigint out of range
+select '9223372036854775800'::int8 * '9223372036854775800'::int8;
+ERROR: bigint out of range
+select '9223372036854775800'::int8 / '0'::int8;
+ERROR: division by zero
+select '9223372036854775800'::int8 % '0'::int8;
+ERROR: division by zero
+select abs('-9223372036854775808'::int8);
+ERROR: bigint out of range
+select '9223372036854775800'::int8 + '100'::int4;
+ERROR: bigint out of range
+select '-9223372036854775800'::int8 - '100'::int4;
+ERROR: bigint out of range
+select '9223372036854775800'::int8 * '100'::int4;
+ERROR: bigint out of range
+select '100'::int4 + '9223372036854775800'::int8;
+ERROR: bigint out of range
+select '-100'::int4 - '9223372036854775800'::int8;
+ERROR: bigint out of range
+select '100'::int4 * '9223372036854775800'::int8;
+ERROR: bigint out of range
+select '9223372036854775800'::int8 + '100'::int2;
+ERROR: bigint out of range
+select '-9223372036854775800'::int8 - '100'::int2;
+ERROR: bigint out of range
+select '9223372036854775800'::int8 * '100'::int2;
+ERROR: bigint out of range
+select '-9223372036854775808'::int8 / '0'::int2;
+ERROR: division by zero
+select '100'::int2 + '9223372036854775800'::int8;
+ERROR: bigint out of range
+select '-100'::int2 - '9223372036854775800'::int8;
+ERROR: bigint out of range
+select '100'::int2 * '9223372036854775800'::int8;
+ERROR: bigint out of range
+select '100'::int2 / '0'::int8;
+ERROR: division by zero
+SELECT CAST(q1 AS int4) FROM int8_tbl WHERE q2 = 456;
+ q1
+-----
+ 123
+(1 row)
+
+SELECT CAST(q1 AS int4) FROM int8_tbl WHERE q2 <> 456;
+ERROR: integer out of range
+SELECT CAST(q1 AS int2) FROM int8_tbl WHERE q2 = 456;
+ q1
+-----
+ 123
+(1 row)
+
+SELECT CAST(q1 AS int2) FROM int8_tbl WHERE q2 <> 456;
+ERROR: smallint out of range
+SELECT CAST('42'::int2 AS int8), CAST('-37'::int2 AS int8);
+ int8 | int8
+------+------
+ 42 | -37
+(1 row)
+
+SELECT CAST(q1 AS float4), CAST(q2 AS float8) FROM INT8_TBL;
+ q1 | q2
+-------------+------------------------
+ 123 | 456
+ 123 | 4.567890123456789e+15
+ 4.56789e+15 | 123
+ 4.56789e+15 | 4.567890123456789e+15
+ 4.56789e+15 | -4.567890123456789e+15
+(5 rows)
+
+SELECT CAST('36854775807.0'::float4 AS int8);
+ int8
+-------------
+ 36854775808
+(1 row)
+
+SELECT CAST('922337203685477580700.0'::float8 AS int8);
+ERROR: bigint out of range
+SELECT CAST(q1 AS oid) FROM INT8_TBL;
+ERROR: OID out of range
+SELECT oid::int8 FROM pg_class WHERE relname = 'pg_class';
+ oid
+------
+ 1259
+(1 row)
+
+-- bit operations
+SELECT q1, q2, q1 & q2 AS "and", q1 | q2 AS "or", q1 # q2 AS "xor", ~q1 AS "not" FROM INT8_TBL;
+ q1 | q2 | and | or | xor | not
+------------------+-------------------+------------------+------------------+------------------+-------------------
+ 123 | 456 | 72 | 507 | 435 | -124
+ 123 | 4567890123456789 | 17 | 4567890123456895 | 4567890123456878 | -124
+ 4567890123456789 | 123 | 17 | 4567890123456895 | 4567890123456878 | -4567890123456790
+ 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789 | 0 | -4567890123456790
+ 4567890123456789 | -4567890123456789 | 1 | -1 | -2 | -4567890123456790
+(5 rows)
+
+SELECT q1, q1 << 2 AS "shl", q1 >> 3 AS "shr" FROM INT8_TBL;
+ q1 | shl | shr
+------------------+-------------------+-----------------
+ 123 | 492 | 15
+ 123 | 492 | 15
+ 4567890123456789 | 18271560493827156 | 570986265432098
+ 4567890123456789 | 18271560493827156 | 570986265432098
+ 4567890123456789 | 18271560493827156 | 570986265432098
+(5 rows)
+
+-- generate_series
+SELECT * FROM generate_series('+4567890123456789'::int8, '+4567890123456799'::int8);
+ generate_series
+------------------
+ 4567890123456789
+ 4567890123456790
+ 4567890123456791
+ 4567890123456792
+ 4567890123456793
+ 4567890123456794
+ 4567890123456795
+ 4567890123456796
+ 4567890123456797
+ 4567890123456798
+ 4567890123456799
+(11 rows)
+
+SELECT * FROM generate_series('+4567890123456789'::int8, '+4567890123456799'::int8, 0);
+ERROR: step size cannot equal zero
+SELECT * FROM generate_series('+4567890123456789'::int8, '+4567890123456799'::int8, 2);
+ generate_series
+------------------
+ 4567890123456789
+ 4567890123456791
+ 4567890123456793
+ 4567890123456795
+ 4567890123456797
+ 4567890123456799
+(6 rows)
+
+-- corner case
+SELECT (-1::int8<<63)::text;
+ text
+----------------------
+ -9223372036854775808
+(1 row)
+
+SELECT ((-1::int8<<63)+1)::text;
+ text
+----------------------
+ -9223372036854775807
+(1 row)
+
+-- check sane handling of INT64_MIN overflow cases
+SELECT (-9223372036854775808)::int8 * (-1)::int8;
+ERROR: bigint out of range
+SELECT (-9223372036854775808)::int8 / (-1)::int8;
+ERROR: bigint out of range
+SELECT (-9223372036854775808)::int8 % (-1)::int8;
+ ?column?
+----------
+ 0
+(1 row)
+
+SELECT (-9223372036854775808)::int8 * (-1)::int4;
+ERROR: bigint out of range
+SELECT (-9223372036854775808)::int8 / (-1)::int4;
+ERROR: bigint out of range
+SELECT (-9223372036854775808)::int8 % (-1)::int4;
+ ?column?
+----------
+ 0
+(1 row)
+
+SELECT (-9223372036854775808)::int8 * (-1)::int2;
+ERROR: bigint out of range
+SELECT (-9223372036854775808)::int8 / (-1)::int2;
+ERROR: bigint out of range
+SELECT (-9223372036854775808)::int8 % (-1)::int2;
+ ?column?
+----------
+ 0
+(1 row)
+
+-- check rounding when casting from float
+SELECT x, x::int8 AS int8_value
+FROM (VALUES (-2.5::float8),
+ (-1.5::float8),
+ (-0.5::float8),
+ (0.0::float8),
+ (0.5::float8),
+ (1.5::float8),
+ (2.5::float8)) t(x);
+ x | int8_value
+------+------------
+ -2.5 | -2
+ -1.5 | -2
+ -0.5 | 0
+ 0 | 0
+ 0.5 | 0
+ 1.5 | 2
+ 2.5 | 2
+(7 rows)
+
+-- check rounding when casting from numeric
+SELECT x, x::int8 AS int8_value
+FROM (VALUES (-2.5::numeric),
+ (-1.5::numeric),
+ (-0.5::numeric),
+ (0.0::numeric),
+ (0.5::numeric),
+ (1.5::numeric),
+ (2.5::numeric)) t(x);
+ x | int8_value
+------+------------
+ -2.5 | -3
+ -1.5 | -2
+ -0.5 | -1
+ 0.0 | 0
+ 0.5 | 1
+ 1.5 | 2
+ 2.5 | 3
+(7 rows)
+
+-- test gcd()
+SELECT a, b, gcd(a, b), gcd(a, -b), gcd(b, a), gcd(-b, a)
+FROM (VALUES (0::int8, 0::int8),
+ (0::int8, 29893644334::int8),
+ (288484263558::int8, 29893644334::int8),
+ (-288484263558::int8, 29893644334::int8),
+ ((-9223372036854775808)::int8, 1::int8),
+ ((-9223372036854775808)::int8, 9223372036854775807::int8),
+ ((-9223372036854775808)::int8, 4611686018427387904::int8)) AS v(a, b);
+ a | b | gcd | gcd | gcd | gcd
+----------------------+---------------------+---------------------+---------------------+---------------------+---------------------
+ 0 | 0 | 0 | 0 | 0 | 0
+ 0 | 29893644334 | 29893644334 | 29893644334 | 29893644334 | 29893644334
+ 288484263558 | 29893644334 | 6835958 | 6835958 | 6835958 | 6835958
+ -288484263558 | 29893644334 | 6835958 | 6835958 | 6835958 | 6835958
+ -9223372036854775808 | 1 | 1 | 1 | 1 | 1
+ -9223372036854775808 | 9223372036854775807 | 1 | 1 | 1 | 1
+ -9223372036854775808 | 4611686018427387904 | 4611686018427387904 | 4611686018427387904 | 4611686018427387904 | 4611686018427387904
+(7 rows)
+
+SELECT gcd((-9223372036854775808)::int8, 0::int8); -- overflow
+ERROR: bigint out of range
+SELECT gcd((-9223372036854775808)::int8, (-9223372036854775808)::int8); -- overflow
+ERROR: bigint out of range
+-- test lcm()
+SELECT a, b, lcm(a, b), lcm(a, -b), lcm(b, a), lcm(-b, a)
+FROM (VALUES (0::int8, 0::int8),
+ (0::int8, 29893644334::int8),
+ (29893644334::int8, 29893644334::int8),
+ (288484263558::int8, 29893644334::int8),
+ (-288484263558::int8, 29893644334::int8),
+ ((-9223372036854775808)::int8, 0::int8)) AS v(a, b);
+ a | b | lcm | lcm | lcm | lcm
+----------------------+-------------+------------------+------------------+------------------+------------------
+ 0 | 0 | 0 | 0 | 0 | 0
+ 0 | 29893644334 | 0 | 0 | 0 | 0
+ 29893644334 | 29893644334 | 29893644334 | 29893644334 | 29893644334 | 29893644334
+ 288484263558 | 29893644334 | 1261541684539134 | 1261541684539134 | 1261541684539134 | 1261541684539134
+ -288484263558 | 29893644334 | 1261541684539134 | 1261541684539134 | 1261541684539134 | 1261541684539134
+ -9223372036854775808 | 0 | 0 | 0 | 0 | 0
+(6 rows)
+
+SELECT lcm((-9223372036854775808)::int8, 1::int8); -- overflow
+ERROR: bigint out of range
+SELECT lcm(9223372036854775807::int8, 9223372036854775806::int8); -- overflow
+ERROR: bigint out of range
generated by cgit v1.2.3 (git 2.39.1) at 2024-11-17 11:59:41 +0000