numeric.out

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

    FROM num_data
    WHERE val != '0.0';
SELECT t1.id1, t1.result, t2.expected
    FROM num_result t1, num_exp_ln t2
    WHERE t1.id1 = t2.id
    AND t1.result != t2.expected;
 id1 | result | expected 
-----+--------+----------
(0 rows)

-- ******************************
-- * Logarithm base 10 check
-- ******************************
DELETE FROM num_result;
INSERT INTO num_result SELECT id, 0, LOG(numeric '10', ABS(val))
    FROM num_data
    WHERE val != '0.0';
SELECT t1.id1, t1.result, t2.expected
    FROM num_result t1, num_exp_log10 t2
    WHERE t1.id1 = t2.id
    AND t1.result != t2.expected;
 id1 | result | expected 
-----+--------+----------
(0 rows)

-- ******************************
-- * POWER(10, LN(value)) check
-- ******************************
DELETE FROM num_result;
INSERT INTO num_result SELECT id, 0, POWER(numeric '10', LN(ABS(round(val,200))))
    FROM num_data
    WHERE val != '0.0';
SELECT t1.id1, t1.result, t2.expected
    FROM num_result t1, num_exp_power_10_ln t2
    WHERE t1.id1 = t2.id
    AND t1.result != t2.expected;
 id1 | result | expected 
-----+--------+----------
(0 rows)

-- ******************************
-- * miscellaneous checks for things that have been broken in the past...
-- ******************************
-- numeric AVG used to fail on some platforms
SELECT AVG(val) FROM num_data;
          avg           
------------------------
 -13430913.592242320700
(1 row)

SELECT STDDEV(val) FROM num_data;
            stddev             
-------------------------------
 27791203.28758835329805617386
(1 row)

SELECT VARIANCE(val) FROM num_data;
               variance               
--------------------------------------
 772350980172061.69659105821915863601
(1 row)

-- Check for appropriate rounding and overflow
CREATE TABLE fract_only (id int, val numeric(4,4));
INSERT INTO fract_only VALUES (1, '0.0');
INSERT INTO fract_only VALUES (2, '0.1');
INSERT INTO fract_only VALUES (3, '1.0');	-- should fail
ERROR:  numeric field overflow
DETAIL:  The absolute value is greater than or equal to 10^0 for field with precision 4, scale 4.
INSERT INTO fract_only VALUES (4, '-0.9999');
INSERT INTO fract_only VALUES (5, '0.99994');
INSERT INTO fract_only VALUES (6, '0.99995');  -- should fail
ERROR:  numeric field overflow
DETAIL:  The absolute value is greater than or equal to 10^0 for field with precision 4, scale 4.
INSERT INTO fract_only VALUES (7, '0.00001');
INSERT INTO fract_only VALUES (8, '0.00017');
SELECT * FROM fract_only;
 id |   val   
----+---------
  1 |  0.0000
  2 |  0.1000
  4 | -0.9999
  5 |  0.9999
  7 |  0.0000
  8 |  0.0002
(6 rows)

DROP TABLE fract_only;
-- Simple check that ceil(), floor(), and round() work correctly
CREATE TABLE ceil_floor_round (a numeric);
INSERT INTO ceil_floor_round VALUES ('-5.5');
INSERT INTO ceil_floor_round VALUES ('-5.499999');
INSERT INTO ceil_floor_round VALUES ('9.5');
INSERT INTO ceil_floor_round VALUES ('9.4999999');
INSERT INTO ceil_floor_round VALUES ('0.0');
INSERT INTO ceil_floor_round VALUES ('0.0000001');
INSERT INTO ceil_floor_round VALUES ('-0.000001');
SELECT a, ceil(a), ceiling(a), floor(a), round(a) FROM ceil_floor_round;
     a     | ceil | ceiling | floor | round 
-----------+------+---------+-------+-------
      -5.5 |   -5 |      -5 |    -6 |    -6
 -5.499999 |   -5 |      -5 |    -6 |    -5
       9.5 |   10 |      10 |     9 |    10
 9.4999999 |   10 |      10 |     9 |     9
       0.0 |    0 |       0 |     0 |     0
 0.0000001 |    1 |       1 |     0 |     0
 -0.000001 |    0 |       0 |    -1 |     0
(7 rows)

DROP TABLE ceil_floor_round;
-- Testing for width_bucket()
-- NULL result
SELECT width_bucket(NULL, NULL, NULL, NULL);
 width_bucket 
--------------
             
(1 row)

-- errors
SELECT width_bucket(5.0, 3.0, 4.0, 0);
ERROR:  count must be greater than zero
SELECT width_bucket(5.0, 3.0, 4.0, -5);
ERROR:  count must be greater than zero
SELECT width_bucket(3.0, 3.0, 3.0, 888);
ERROR:  lower bound cannot equal upper bound
-- normal operation
CREATE TABLE width_bucket_test (operand numeric);
COPY width_bucket_test FROM stdin;
SELECT
    operand,
    width_bucket(operand, 0, 10, 5) AS wb_1,
    width_bucket(operand, 10, 0, 5) AS wb_2,
    width_bucket(operand, 2, 8, 4) AS wb_3,
    width_bucket(operand, 5.0, 5.5, 20) AS wb_4,
    width_bucket(operand, -25, 25, 10) AS wb_5
    FROM width_bucket_test;
     operand      | wb_1 | wb_2 | wb_3 | wb_4 | wb_5 
------------------+------+------+------+------+------
             -5.2 |    0 |    6 |    0 |    0 |    4
 -0.0000000000001 |    0 |    6 |    0 |    0 |    5
  0.0000000000001 |    1 |    5 |    0 |    0 |    6
                1 |    1 |    5 |    0 |    0 |    6
 1.99999999999999 |    1 |    5 |    0 |    0 |    6
                2 |    2 |    5 |    1 |    0 |    6
 2.00000000000001 |    2 |    4 |    1 |    0 |    6
                3 |    2 |    4 |    1 |    0 |    6
                4 |    3 |    4 |    2 |    0 |    6
              4.5 |    3 |    3 |    2 |    0 |    6
                5 |    3 |    3 |    3 |    1 |    7
              5.5 |    3 |    3 |    3 |   21 |    7
                6 |    4 |    3 |    3 |   21 |    7
                7 |    4 |    2 |    4 |   21 |    7
                8 |    5 |    2 |    5 |   21 |    7
                9 |    5 |    1 |    5 |   21 |    7
 9.99999999999999 |    5 |    1 |    5 |   21 |    7
               10 |    6 |    1 |    5 |   21 |    8
 10.0000000000001 |    6 |    0 |    5 |   21 |    8
              NaN |    6 |    0 |    5 |   21 |   11
(20 rows)

DROP TABLE width_bucket_test;
-- TO_CHAR()
--
SELECT '' AS to_char_1, to_char(val, '9G999G999G999G999G999') 
	FROM num_data;
 to_char_1 |        to_char         
-----------+------------------------
           |                      0
           |                      0
           |            -34,338,492
           |                      4
           |              7,799,461
           |                 16,397
           |                 93,902
           |            -83,028,485
           |                 74,881
           |            -24,926,804
(10 rows)

SELECT '' AS to_char_2, to_char(val, '9G999G999G999G999G999D999G999G999G999G999')
	FROM num_data;	
 to_char_2 |                  to_char                   
-----------+--------------------------------------------
           |                       .000,000,000,000,000
           |                       .000,000,000,000,000
           |            -34,338,492.215,397,047,000,000
           |                      4.310,000,000,000,000
           |              7,799,461.411,900,000,000,000
           |                 16,397.038,491,000,000,000
           |                 93,901.577,630,260,000,000
           |            -83,028,485.000,000,000,000,000
           |                 74,881.000,000,000,000,000
           |            -24,926,804.045,047,420,000,000
(10 rows)

SELECT '' AS to_char_3, to_char(val, '9999999999999999.999999999999999PR')
	FROM num_data;
 to_char_3 |              to_char               
-----------+------------------------------------
           |                  .000000000000000 
           |                  .000000000000000 
           |         <34338492.215397047000000>
           |                 4.310000000000000 
           |           7799461.411900000000000 
           |             16397.038491000000000 
           |             93901.577630260000000 
           |         <83028485.000000000000000>
           |             74881.000000000000000 
           |         <24926804.045047420000000>
(10 rows)

SELECT '' AS to_char_4, to_char(val, '9999999999999999.999999999999999S')
	FROM num_data;
 to_char_4 |              to_char              
-----------+-----------------------------------
           |                 .000000000000000+
           |                 .000000000000000+
           |         34338492.215397047000000-
           |                4.310000000000000+
           |          7799461.411900000000000+
           |            16397.038491000000000+
           |            93901.577630260000000+
           |         83028485.000000000000000-
           |            74881.000000000000000+
           |         24926804.045047420000000-
(10 rows)

SELECT '' AS to_char_5,  to_char(val, 'MI9999999999999999.999999999999999')     FROM num_data;	
 to_char_5 |              to_char              
-----------+-----------------------------------
           |                  .000000000000000
           |                  .000000000000000
           | -        34338492.215397047000000
           |                 4.310000000000000
           |           7799461.411900000000000
           |             16397.038491000000000
           |             93901.577630260000000
           | -        83028485.000000000000000
           |             74881.000000000000000
           | -        24926804.045047420000000
(10 rows)

SELECT '' AS to_char_6,  to_char(val, 'FMS9999999999999999.999999999999999')    FROM num_data;
 to_char_6 |       to_char       
-----------+---------------------
           | +0.
           | +0.
           | -34338492.215397047
           | +4.31
           | +7799461.4119
           | +16397.038491
           | +93901.57763026
           | -83028485.
           | +74881.
           | -24926804.04504742
(10 rows)

SELECT '' AS to_char_7,  to_char(val, 'FM9999999999999999.999999999999999THPR') FROM num_data;
 to_char_7 |       to_char        
-----------+----------------------
           | 0.
           | 0.
           | <34338492.215397047>
           | 4.31
           | 7799461.4119
           | 16397.038491
           | 93901.57763026
           | <83028485.>
           | 74881.
           | <24926804.04504742>
(10 rows)

SELECT '' AS to_char_8,  to_char(val, 'SG9999999999999999.999999999999999th')   FROM num_data;	
 to_char_8 |              to_char              
-----------+-----------------------------------
           | +                .000000000000000
           | +                .000000000000000
           | -        34338492.215397047000000
           | +               4.310000000000000
           | +         7799461.411900000000000
           | +           16397.038491000000000
           | +           93901.577630260000000
           | -        83028485.000000000000000
           | +           74881.000000000000000
           | -        24926804.045047420000000
(10 rows)

SELECT '' AS to_char_9,  to_char(val, '0999999999999999.999999999999999')       FROM num_data;	
 to_char_9 |              to_char              
-----------+-----------------------------------
           |  0000000000000000.000000000000000
           |  0000000000000000.000000000000000
           | -0000000034338492.215397047000000
           |  0000000000000004.310000000000000
           |  0000000007799461.411900000000000
           |  0000000000016397.038491000000000
           |  0000000000093901.577630260000000
           | -0000000083028485.000000000000000
           |  0000000000074881.000000000000000
           | -0000000024926804.045047420000000
(10 rows)

SELECT '' AS to_char_10, to_char(val, 'S0999999999999999.999999999999999')      FROM num_data;	
 to_char_10 |              to_char              
------------+-----------------------------------
            | +0000000000000000.000000000000000
            | +0000000000000000.000000000000000
            | -0000000034338492.215397047000000
            | +0000000000000004.310000000000000
            | +0000000007799461.411900000000000
            | +0000000000016397.038491000000000