int8.out

PostgreSQL 8.1.4的源码 适用于Linux下的开源数据库系统

--
-- 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 integer: "      "
INSERT INTO INT8_TBL(q1) VALUES ('xxx');
ERROR:  invalid input syntax for integer: "xxx"
INSERT INTO INT8_TBL(q1) VALUES ('3908203590239580293850293850329485');
ERROR:  value "3908203590239580293850293850329485" is out of range for type bigint
INSERT INTO INT8_TBL(q1) VALUES ('-1204982019841029840928340329840934');
ERROR:  value "-1204982019841029840928340329840934" is out of range for type bigint
INSERT INTO INT8_TBL(q1) VALUES ('- 123');
ERROR:  invalid input syntax for integer: "- 123"
INSERT INTO INT8_TBL(q1) VALUES ('  345     5');
ERROR:  invalid input syntax for integer: "  345     5"
INSERT INTO INT8_TBL(q1) VALUES ('');
ERROR:  invalid input syntax for integer: ""
SELECT * FROM INT8_TBL;
        q1        |        q2         
------------------+-------------------
              123 |               456
              123 |  4567890123456789
 4567890123456789 |               123
 4567890123456789 |  4567890123456789
 4567890123456789 | -4567890123456789
(5 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 FROM INT8_TBL;
 five |        q1        |        q2         |     divide     
------+------------------+-------------------+----------------
      |              123 |               456 |              0
      |              123 |  4567890123456789 |              0
      | 4567890123456789 |               123 | 37137318076884
      | 4567890123456789 |  4567890123456789 |              1
      | 4567890123456789 | -4567890123456789 |             -1
(5 rows)

SELECT '' AS five, q1, float8(q1) FROM INT8_TBL;
 five |        q1        |        float8        
------+------------------+----------------------
      |              123 |                  123
      |              123 |                  123
      | 4567890123456789 | 4.56789012345679e+15
      | 4567890123456789 | 4.56789012345679e+15
      | 4567890123456789 | 4.56789012345679e+15
(5 rows)

SELECT '' AS five, q2, float8(q2) FROM INT8_TBL;
 five |        q2         |        float8         
------+-------------------+-----------------------
      |               456 |                   456
      |  4567890123456789 |  4.56789012345679e+15
      |               123 |                   123
      |  4567890123456789 |  4.56789012345679e+15
      | -4567890123456789 | -4.56789012345679e+15
(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)

-- 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
            |  [phone] 9 . 0 0 0
            |                            +1 2 3 . 0 0 0
            |  [phone] 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
select '-9223372036854775808'::int8;
         int8         
----------------------
 -9223372036854775808
(1 row)

select '-9223372036854775809'::int8;
ERROR:  value "-9223372036854775809" is out of range for type bigint
select '9223372036854775807'::int8;
        int8         
---------------------
 9223372036854775807
(1 row)

select '9223372036854775808'::int8;
ERROR:  value "9223372036854775808" is out of range for type bigint