Mac De Oracle Heterogeneous! #25 Tweet
前回までで日付、時間型の癖は把握できた。ここからは数値型について見ていく。まず初めに、PowerBook G4 1Ghz MacOSX Tiger 10.4.4 (アップデータしたので）に作成した PostgreSQL7.4.9から。
以下のような表をPostgreSQl7.4.9に作成した。
pb17:˜ postgres$ psql -U scott postgresql749
Password:
Welcome to psql 7.4.9, the PostgreSQL interactive terminal.
Type: ¥copyright for distribution terms
¥h for help with SQL commands
¥? for help on internal slash commands
¥g or terminate with semicolon to execute query
¥q to quit
postgresql749=>
postgresql749=> ¥d num_test
Table "scott.num_test"
Column | Type | Modifiers
----------------+------------------+-----------
r_smallint | smallint |
r_integer | integer |
r_bigint | bigint |
r_numeric38 | numeric(38,0) |
r_numeric38_16 | numeric(38,16) |
r_real | real |
r_double | double precision |
postgresql749=>
正常の登録できて当然なところから。
postgresql749=> insert into num_test values(0,0,0,0,0,0,0);
INSERT 25454 1
postgresql749=> commit;
COMMIT
postgresql749=> insert into num_test values(null,null,null,null,null,null,null);
INSERT 25455 1
postgresql749=> commit;
COMMIT
さて、空文字はどうか？ real型とdouble precision型では空文字を受け入れる。それぞれ ０ が設定される。それ以外の数値型では空文字は許容されない。
postgresql749=> insert into num_test values('',null,null,null,null,null,null);
ERROR: invalid input syntax for integer: ""
postgresql749=> rollback;
ROLLBACK
postgresql749=> insert into num_test values(null,'',null,null,null,null,null);
ERROR: invalid input syntax for integer: ""
postgresql749=> rollback;
ROLLBACK
postgresql749=> insert into num_test values(null,null,'',null,null,null,null);
ERROR: invalid input syntax for type bigint: ""
postgresql749=> rollback;
ROLLBACK
postgresql749=> insert into num_test values(null,null,null,'',null,null,null);
ERROR: invalid input syntax for type numeric: ""
postgresql749=> rollback;
ROLLBACK
postgresql749=> insert into num_test values(null,null,null,null,'',null,null);
ERROR: invalid input syntax for type numeric: ""
postgresql749=> rollback;
ROLLBACK
postgresql749=> insert into num_test values(null,null,null,null,null,'',null);
INSERT 25456 1
postgresql749=> commit;
COMMIT
postgresql749=> insert into num_test values(null,null,null,null,null,null,'');
INSERT 25457 1
postgresql749=> commit;
COMMIT
postgresql749=> select * from num_test;
r_smallint | r_integer | r_bigint | r_numeric38 | r_numeric38_16 | r_real | r_double
------------+-----------+----------+-------------+--------------------+--------+----------
0 | 0 | 0 | 0 | 0.0000000000000000 | 0 | 0
| | | | | |
| | | | | 0 |
| | | | | | 0
(4 rows)
postgresql749=>
次はsmallint型。変わった癖は無い。
postgresql749=> insert into num_test(r_smallint) values(32767);
INSERT 25458 1
postgresql749=> insert into num_test(r_smallint) values(-32768);
INSERT 25459 1
postgresql749=> commit;
COMMIT
postgresql749=> insert into num_test(r_smallint) values(32768);
ERROR: integer out of range
postgresql749=> rollback;
ROLLBACK
postgresql749=> insert into num_test(r_smallint) values(-32769);
ERROR: integer out of range
postgresql749=> rollback;
ROLLBACK
postgresql749=>
integer型。
postgresql749=> insert into num_test(r_integer) values(-2147483648);
INSERT 25460 1
postgresql749=> commit;
COMMIT
postgresql749=> insert into num_test(r_integer) values(-2147483649);
ERROR: integer out of range
postgresql749=> rollback;
ROLLBACK
postgresql749=> insert into num_test(r_integer) values(2147483647);
INSERT 25461 1
postgresql749=> commit;
COMMIT
postgresql749=> insert into num_test(r_integer) values(2147483648);
ERROR: integer out of range
postgresql749=> rollback;
ROLLBACK
postgresql749=>
postgresql749=> insert into num_test(r_bigint) values(-9223372036854775808);
INSERT 25462 1
postgresql749=> commit;
COMMIT
postgresql749=> insert into num_test(r_bigint) values(-9223372036854775809);
ERROR: integer out of range
postgresql749=> rollback;
ROLLBACK
postgresql749=> insert into num_test(r_bigint) values(9223372036854775807);
INSERT 25463 1
postgresql749=> commit;
COMMIT
postgresql749=> insert into num_test(r_bigint) values(9223372036854775808);
ERROR: integer out of range
postgresql749=> rollback;
ROLLBACK
postgresql749=>
numeric型で 38,0 の精度。Oracleのnumber型のような使い勝手。
postgresql749=> insert into num_test(r_numeric38) values(-99999999999999999999999999999999999999);
INSERT 25464 1
postgresql749=> commit;
COMMIT
postgresql749=> insert into num_test(r_numeric38) values(-999999999999999999999999999999999999991);
ERROR: numeric field overflow
DETAIL: The absolute value is greater than or equal to 10^38 for field with precision 38, scale 0.
postgresql749=> rollback;
ROLLBACK
postgresql749=> insert into num_test(r_numeric38) values(-100000000000000000000000000000000000000);
ERROR: numeric field overflow
DETAIL: The absolute value is greater than or equal to 10^38 for field with precision 38, scale 0.
postgresql749=> rollback;
ROLLBACK
postgresql749=> insert into num_test(r_numeric38) values(-0.1);
INSERT 25465 1
postgresql749=> insert into num_test(r_numeric38) values(-0.5);
INSERT 25466 1
postgresql749=> insert into num_test(r_numeric38) values(-0.4);
INSERT 25467 1
postgresql749=> commit;
COMMIT
postgresql749=> select * from num_test;
r_smallint | r_integer | r_bigint | r_numeric38 | r_numeric38_16 | r_real | r_double
------------+-------------+----------------------+-----------------------------------------+--------------------+--------+----------
0 | 0 | 0 | 0 | 0.0000000000000000 | 0 | 0
| | | | | |
| | | | | 0 |
| | | | | | 0
32767 | | | | | |
-32768 | | | | | |
| -2147483648 | | | | |
| 2147483647 | | | | |
| | -9223372036854775808 | | | |
| | 9223372036854775807 | | | |
| | | -99999999999999999999999999999999999999 | | |
| | | 0 | | |
| | | -1 | | |
| | | 0 | | |
(14 rows)
精度を変えたnumeric型も変な癖はないようだ。
postgresql749=> insert into num_test(r_numeric38) values(99999999999999999999999999999999999999);
INSERT 25468 1
postgresql749=> commit;
COMMIT
postgresql749=> insert into num_test(r_numeric38) values(100000000000000000000000000000000000000);
ERROR: numeric field overflow
DETAIL: The absolute value is greater than or equal to 10^38 for field with precision 38, scale 0.
postgresql749=> rollback;
ROLLBACK
postgresql749=>
postgresql749=> insert into num_test(r_numeric38_16) values(-9999999999999999999999.9999999999999999);
INSERT 25476 1
postgresql749=> commit;
COMMIT
postgresql749=> insert into num_test(r_numeric38_16) values(-10000000000000000000000.9999999999999999);
ERROR: numeric field overflow
DETAIL: The absolute value is greater than or equal to 10^22 for field with precision 38, scale 16.
postgresql749=> rollback;
ROLLBACK
postgresql749=> insert into num_test(r_numeric38_16) values(9999999999999999999999.9999999999999999);
INSERT 25477 1
postgresql749=> commit;
COMMIT
postgresql749=> insert into num_test(r_numeric38_16) values(10000000000000000000000.9999999999999999);
ERROR: numeric field overflow
DETAIL: The absolute value is greater than or equal to 10^22 for field with precision 38, scale 16.
postgresql749=> rollback;
ROLLBACK
postgresql749=> insert into num_test(r_numeric38_16) values(-9999999999999999999999.99999999999999995);
ERROR: numeric field overflow
DETAIL: The absolute value is greater than or equal to 10^22 for field with precision 38, scale 16.
postgresql749=> rollback;
ROLLBACK
postgresql749=> insert into num_test(r_numeric38_16) values(9999999999999999999999.99999999999999995);
ERROR: numeric field overflow
DETAIL: The absolute value is greater than or equal to 10^22 for field with precision 38, scale 16.
postgresql749=> rollback
postgresql749-> rollback;
ERROR: syntax error at or near "rollback" at character 10
postgresql749=> rollback;
ROLLBACK
postgresql749=> insert into num_test (r_numeric38_16) values(0.99999999999999995);
INSERT 25478 1
postgresql749=> insert into num_test (r_numeric38_16) values(0.99999999999999994);
INSERT 25479 1
postgresql749=> commit;
COMMIT
postgresql749=> select * from num_test;
r_smallint | r_integer | r_bigint | r_numeric38 | r_numeric38_16 | r_real | r_double
------------+-------------+----------------------+-----------------------------------------+------------------------------------------+--------+----------
0 | 0 | 0 | 0 | 0.0000000000000000 | 0 | 0
| | | | | |
| | | | | 0 |
| | | | | | 0
32767 | | | | | |
-32768 | | | | | |
| -2147483648 | | | | |
| 2147483647 | | | | |
| | -9223372036854775808 | | | |
| | 9223372036854775807 | | | |
| | | -99999999999999999999999999999999999999 | | |
| | | 0 | | |
| | | -1 | | |
| | | 0 | | |
| | | 99999999999999999999999999999999999999 | | |
| | | | -9999999999999999999999.9999999999999999 | |
| | | | 9999999999999999999999.9999999999999999 | |
| | | | 1.0000000000000000 | |
| | | | 0.9999999999999999 | |
(19 rows)
postgresql749=>
次は、real型。
postgresql749=>
postgresql749=> insert into num_test(r_real) values(3.4e38);
INSERT 25487 1
postgresql749=> insert into num_test(r_real) values(-3.4e38);
INSERT 25488 1
postgresql749=> commit;
COMMIT
postgresql749=> insert into num_test(r_real) values(1.2e-38);
INSERT 25489 1
postgresql749=> insert into num_test(r_real) values(-1.2e-38);
INSERT 25490 1
postgresql749=> commit;
COMMIT
postgresql749=> insert into num_test(r_real) values(3.4e39);
ERROR: type "real" value out of range: overflow
postgresql749=> rollback;
ROLLBACK
postgresql749=> insert into num_test(r_real) values(-3.4e39);
ERROR: type "real" value out of range: overflow
postgresql749=> rollback;
ROLLBACK
postgresql749=> insert into num_test(r_real) values(-3.5e38);
ERROR: type "real" value out of range: overflow
postgresql749=> rollback;
ROLLBACK
postgresql749=> insert into num_test(r_real) values(-3.41e38);
ERROR: type "real" value out of range: overflow
postgresql749=> rollback;
ROLLBACK
postgresql749=> insert into num_test(r_real) values(1.2e-39);
ERROR: type "real" value out of range: underflow
postgresql749=> rollback;
ROLLBACK
postgresql749=> insert into num_test(r_real) values(1.1e-38);
ERROR: type "real" value out of range: underflow
postgresql749=> rollback;
ROLLBACK
postgresql749=> insert into num_test(r_real) values(-1.1e-38);
ERROR: type "real" value out of range: underflow
postgresql749=> rollback;
ROLLBACK
postgresql749=> select * from num_test;
r_smallint | r_integer | r_bigint | r_numeric38 | r_numeric38_16 | r_real | r_double
------------+-------------+----------------------+-----------------------------------------+------------------------------------------+----------+----------
0 | 0 | 0 | 0 | 0.0000000000000000 | 0 | 0
| | | | | |
| | | | | 0 |
| | | | | | 0
32767 | | | | | |
-32768 | | | | | |
| -2147483648 | | | | |
| 2147483647 | | | | |
| | -9223372036854775808 | | | |
| | 9223372036854775807 | | | |
| | | -99999999999999999999999999999999999999 | | |
| | | 0 | | |
| | | -1 | | |
| | | 0 | | |
| | | 99999999999999999999999999999999999999 | | |
| | | | -9999999999999999999999.9999999999999999 | |
| | | | 9999999999999999999999.9999999999999999 | |
| | | | 1.0000000000000000 | |
| | | | 0.9999999999999999 | |
| | | | | 3.4e+38 |
| | | | | -3.4e+38 |
| | | | | 1.2e-38 |
| | | | | -1.2e-38 |
(23 rows)
postgresql749=>
最後に double precision型。
postgresql749=> insert into num_test(r_double) values(1.79e308);
INSERT 25495 1
postgresql749=> insert into num_test(r_double) values(-1.79e308);
INSERT 25496 1
postgresql749=> insert into num_test(r_double) values(2.3e-308);
INSERT 25497 1
postgresql749=> insert into num_test(r_double) values(-2.3e-308);
INSERT 25498 1
postgresql749=> commit;
COMMIT
postgresql749=> insert into num_test(r_double) values(1.80e308);
ERROR:"180000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
000000000000000000000000000000000000000000" is out of range for type double precision
postgresql749=> rollback;
ROLLBACK
postgresql749=> insert into num_test(r_double) values(-1.80e308);
ERROR:"-18000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000" is out of range for type double precision
postgresql749=> rollback;
ROLLBACK
postgresql749=> insert into num_test(r_double) values(1.79e309);
ERROR:"179000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
0000000000000000000000000000000000000000000" is out of range for type double precision
postgresql749=> rollback;
ROLLBACK
postgresql749=> insert into num_test(r_double) values(-1.79e309);
ERROR:"-17900000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
00000000000000000000000000000000000000000000" is out of range for type double precision
postgresql749=> rollback;
ROLLBACK
postgresql749=> insert into num_test(r_double) values(2.2e-308);
ERROR: type "double precision" value out of range: underflow
postgresql749=> rollback;
ROLLBACK
postgresql749=> insert into num_test(r_double) values(-2.2e-308);
ERROR: type "double precision" value out of range: underflow
postgresql749=> rollback;
ROLLBACK
postgresql749=> insert into num_test(r_double) values(2.3e-309);
ERROR: type "double precision" value out of range: underflow
postgresql749=> rollback;
ROLLBACK
postgresql749=> insert into num_test(r_double) values(-2.3e-309);
ERROR: type "double precision" value out of range: underflow
postgresql749=> rollback;
ROLLBACK
postgresql749=> select * from num_test;
r_smallint | r_integer | r_bigint | r_numeric38 | r_numeric38_16 | r_real | r_double
------------+-------------+----------------------+-----------------------------------------+------------------------------------------+----------+------------
0 | 0 | 0 | 0 | 0.0000000000000000 | 0 | 0
| | | | | |
| | | | | 0 |
| | | | | | 0
32767 | | | | | |
-32768 | | | | | |
| -2147483648 | | | | |
| 2147483647 | | | | |
| | -9223372036854775808 | | | |
| | 9223372036854775807 | | | |
| | | -99999999999999999999999999999999999999 | | |
| | | 0 | | |
| | | -1 | | |
| | | 0 | | |
| | | 99999999999999999999999999999999999999 | | |
| | | | -9999999999999999999999.9999999999999999 | |
| | | | 9999999999999999999999.9999999999999999 | |
| | | | 1.0000000000000000 | |
| | | | 0.9999999999999999 | |
| | | | | 3.4e+38 |
| | | | | -3.4e+38 |
| | | | | 1.2e-38 |
| | | | | -1.2e-38 |
| | | | | | 1.79e+308
| | | | | | -1.79e+308
| | | | | | 2.3e-308
| | | | | | -2.3e-308
(27 rows)
postgresql749=>
postgresql749=> ¥q
pb17:˜ postgres$
日付型とは異なり変わった癖はなさそうな予感。次回はgeneric connectivity経由でOracleからアクセスしてみる。
| 固定リンク | 0
コメント