path: root/yql/essentials/tests/postgresql/original/cases/int8.out

--
-- 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 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 '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