Google Git
Sign in
apache / cloudberry / refs/heads/cbdb-postgres-merge / . / src / test / regress / expected / int8.out
blob: 02b711f925086c864a9ad3565d53743482a49e1f [file] [log] [blame]
--
-- INT8
-- Test int8 64-bit integers.
--
-- int8_tbl was already created and filled in test_setup.sql.
-- Here we just try to insert bad values.
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 ('');
^
-- The queries in this file are not sensitive to the stats, but
-- other more complicated queries in other test files are.
ANALYZE int8_tbl;
SELECT * FROM INT8_TBL;
q1 | q2
------------------+-------------------
123 | 456
123 | 4567890123456789
4567890123456789 | 123
4567890123456789 | 4567890123456789
4567890123456789 | -4567890123456789
(5 rows)
-- Also try it with non-error-throwing API
SELECT pg_input_is_valid('34', 'int8');
pg_input_is_valid
-------------------
t
(1 row)
SELECT pg_input_is_valid('asdf', 'int8');
pg_input_is_valid
-------------------
f
(1 row)
SELECT pg_input_is_valid('10000000000000000000', 'int8');
pg_input_is_valid
-------------------
f
(1 row)
SELECT * FROM pg_input_error_info('10000000000000000000', 'int8');
message | detail | hint | sql_error_code
--------------------------------------------------------------+--------+------+----------------
value "10000000000000000000" is out of range for type bigint | | | 22003
(1 row)
-- 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 q1 AS plus, -q1 AS minus FROM INT8_TBL;
plus | minus
------------------+-------------------
123 | -123
123 | -123
4567890123456789 | -4567890123456789
4567890123456789 | -4567890123456789
4567890123456789 | -4567890123456789
(5 rows)
SELECT q1, q2, q1 + q2 AS plus FROM INT8_TBL;
q1 | q2 | plus
------------------+-------------------+------------------
123 | 456 | 579
123 | 4567890123456789 | 4567890123456912
4567890123456789 | 123 | 4567890123456912
4567890123456789 | 4567890123456789 | 9135780246913578
4567890123456789 | -4567890123456789 | 0
(5 rows)
SELECT q1, q2, q1 - q2 AS minus FROM INT8_TBL;
q1 | q2 | minus
------------------+-------------------+-------------------
123 | 456 | -333
123 | 4567890123456789 | -4567890123456666
4567890123456789 | 123 | 4567890123456666
4567890123456789 | 4567890123456789 | 0
4567890123456789 | -4567890123456789 | 9135780246913578
(5 rows)
SELECT q1, q2, q1 * q2 AS multiply FROM INT8_TBL;
ERROR: bigint out of range
SELECT q1, q2, q1 * q2 AS multiply FROM INT8_TBL
WHERE q1 < 1000 or (q2 > 0 and q2 < 1000);
q1 | q2 | multiply
------------------+------------------+--------------------
123 | 456 | 56088
123 | 4567890123456789 | 561850485185185047
4567890123456789 | 123 | 561850485185185047
(3 rows)
SELECT q1, q2, q1 / q2 AS divide, q1 % q2 AS mod FROM INT8_TBL;
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 q1, float8(q1) FROM INT8_TBL;
q1 | float8
------------------+-----------------------
123 | 123
123 | 123
4567890123456789 | 4.567890123456789e+15
4567890123456789 | 4.567890123456789e+15
4567890123456789 | 4.567890123456789e+15
(5 rows)
SELECT q2, float8(q2) FROM INT8_TBL;
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 2 * q1 AS "twice int4" FROM INT8_TBL;
twice int4
------------------
246
246
9135780246913578
9135780246913578
9135780246913578
(5 rows)
SELECT q1 * 2 AS "twice int4" FROM INT8_TBL;
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 to_char(q1, '9G999G999G999G999G999'), to_char(q2, '9,999,999,999,999,999')
FROM INT8_TBL;
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 to_char(q1, '9G999G999G999G999G999D999G999'), to_char(q2, '9,999,999,999,999,999.999,999')
FROM INT8_TBL;
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 to_char( (q1 * -1), '9999999999999999PR'), to_char( (q2 * -1), '9999999999999999.999PR')
FROM INT8_TBL;
to_char | to_char
--------------------+------------------------
<123> | <456.000>
<123> | <4567890123456789.000>
<4567890123456789> | <123.000>
<4567890123456789> | <4567890123456789.000>
<4567890123456789> | 4567890123456789.000
(5 rows)
SELECT to_char( (q1 * -1), '9999999999999999S'), to_char( (q2 * -1), 'S9999999999999999')
FROM INT8_TBL;
to_char | to_char
-------------------+-------------------
123- | -456
123- | -4567890123456789
4567890123456789- | -123
4567890123456789- | -4567890123456789
4567890123456789- | +4567890123456789
(5 rows)
SELECT to_char(q2, 'MI9999999999999999') FROM INT8_TBL;
to_char
-------------------
456
4567890123456789
123
4567890123456789
-4567890123456789
(5 rows)
SELECT to_char(q2, 'FMS9999999999999999') FROM INT8_TBL;
to_char
-------------------
+456
+4567890123456789
+123
+4567890123456789
-4567890123456789
(5 rows)
SELECT to_char(q2, 'FM9999999999999999THPR') FROM INT8_TBL;
to_char
--------------------
456TH
4567890123456789TH
123RD
4567890123456789TH
<4567890123456789>
(5 rows)
SELECT to_char(q2, 'SG9999999999999999th') FROM INT8_TBL;
to_char
---------------------
+ 456th
+4567890123456789th
+ 123rd
+4567890123456789th
-4567890123456789
(5 rows)
SELECT to_char(q2, '0999999999999999') FROM INT8_TBL;
to_char
-------------------
0000000000000456
4567890123456789
0000000000000123
4567890123456789
-4567890123456789
(5 rows)
SELECT to_char(q2, 'S0999999999999999') FROM INT8_TBL;
to_char
-------------------
+0000000000000456
+4567890123456789
+0000000000000123
+4567890123456789
-4567890123456789
(5 rows)
SELECT to_char(q2, 'FM0999999999999999') FROM INT8_TBL;
to_char
-------------------
0000000000000456
4567890123456789
0000000000000123
4567890123456789
-4567890123456789
(5 rows)
SELECT to_char(q2, 'FM9999999999999999.000') FROM INT8_TBL;
to_char
-----------------------
456.000
4567890123456789.000
123.000
4567890123456789.000
-4567890123456789.000
(5 rows)
SELECT to_char(q2, 'L9999999999999999.000') FROM INT8_TBL;
to_char
------------------------
456.000
4567890123456789.000
123.000
4567890123456789.000
-4567890123456789.000
(5 rows)
SELECT to_char(q2, 'FM9999999999999999.999') FROM INT8_TBL;
to_char
--------------------
456.
4567890123456789.
123.
4567890123456789.
-4567890123456789.
(5 rows)
SELECT 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
-------------------------------------------
+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 to_char(q2, E'99999 "text" 9999 "9999" 999 "\\"text between quote marks\\"" 9999') FROM INT8_TBL;
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 to_char(q2, '999999SG9999999999') FROM INT8_TBL;
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 0::int8 - '-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
-- non-decimal literals
SELECT int8 '0b100101';
int8
------
37
(1 row)
SELECT int8 '0o273';
int8
------
187
(1 row)
SELECT int8 '0x42F';
int8
------
1071
(1 row)
SELECT int8 '0b';
ERROR: invalid input syntax for type bigint: "0b"
LINE 1: SELECT int8 '0b';
^
SELECT int8 '0o';
ERROR: invalid input syntax for type bigint: "0o"
LINE 1: SELECT int8 '0o';
^
SELECT int8 '0x';
ERROR: invalid input syntax for type bigint: "0x"
LINE 1: SELECT int8 '0x';
^
-- cases near overflow
SELECT int8 '0b111111111111111111111111111111111111111111111111111111111111111';
int8
---------------------
9223372036854775807
(1 row)
SELECT int8 '0b1000000000000000000000000000000000000000000000000000000000000000';
ERROR: value "0b1000000000000000000000000000000000000000000000000000000000000000" is out of range for type bigint
LINE 1: SELECT int8 '0b100000000000000000000000000000000000000000000...
^
SELECT int8 '0o777777777777777777777';
int8
---------------------
9223372036854775807
(1 row)
SELECT int8 '0o1000000000000000000000';
ERROR: value "0o1000000000000000000000" is out of range for type bigint
LINE 1: SELECT int8 '0o1000000000000000000000';
^
SELECT int8 '0x7FFFFFFFFFFFFFFF';
int8
---------------------
9223372036854775807
(1 row)
SELECT int8 '0x8000000000000000';
ERROR: value "0x8000000000000000" is out of range for type bigint
LINE 1: SELECT int8 '0x8000000000000000';
^
SELECT int8 '-0b1000000000000000000000000000000000000000000000000000000000000000';
int8
----------------------
-9223372036854775808
(1 row)
SELECT int8 '-0b1000000000000000000000000000000000000000000000000000000000000001';
ERROR: value "-0b1000000000000000000000000000000000000000000000000000000000000001" is out of range for type bigint
LINE 1: SELECT int8 '-0b10000000000000000000000000000000000000000000...
^
SELECT int8 '-0o1000000000000000000000';
int8
----------------------
-9223372036854775808
(1 row)
SELECT int8 '-0o1000000000000000000001';
ERROR: value "-0o1000000000000000000001" is out of range for type bigint
LINE 1: SELECT int8 '-0o1000000000000000000001';
^
SELECT int8 '-0x8000000000000000';
int8
----------------------
-9223372036854775808
(1 row)
SELECT int8 '-0x8000000000000001';
ERROR: value "-0x8000000000000001" is out of range for type bigint
LINE 1: SELECT int8 '-0x8000000000000001';
^
-- underscores
SELECT int8 '1_000_000';
int8
---------
1000000
(1 row)
SELECT int8 '1_2_3';
int8
------
123
(1 row)
SELECT int8 '0x1EEE_FFFF';
int8
-----------
518979583
(1 row)
SELECT int8 '0o2_73';
int8
------
187
(1 row)
SELECT int8 '0b_10_0101';
int8
------
37
(1 row)
-- error cases
SELECT int8 '_100';
ERROR: invalid input syntax for type bigint: "_100"
LINE 1: SELECT int8 '_100';
^
SELECT int8 '100_';
ERROR: invalid input syntax for type bigint: "100_"
LINE 1: SELECT int8 '100_';
^
SELECT int8 '100__000';
ERROR: invalid input syntax for type bigint: "100__000"
LINE 1: SELECT int8 '100__000';
^