SQLのNUMERIC型についてまとめてます。
NUMERICは大きな桁数の数値を格納できるデータ型
NUMERICはSQL ServerやPostgreSQLで扱うことのできるデータ型です。SQL ServerのNUMERIC型は最大38桁までの大きな数値をあらわすことが可能でOracleのNUMBER型に似ています。
【関連記事】
▶SQL NUMBER型は高精度数値を扱う TO_CHARでカンマ編集、0詰めが可能
PostgreSQLのNUMERIC型も大きな数値を格納できるデータ型ですが、取り扱い可能な桁数は10万桁以上の数値が取り扱い可能です。
NUMERIC型は大きな桁数の数値を格納することができる数値型。通貨金額や正確性が求められる数を保存する場合に使われます。ただし、NUMERIC型に対する算術演算は整数型や浮動小数点データ型に比べると非常に遅くなります。
NUMERIC型の最大桁数は 131,072桁の整数部と16,383桁の小数部。以下の例は、numeric型のカラムを持つテーブルtbl1に、整数部20桁、小数部20桁の数値をinsertしたものです。
postgres=# create table tbl1 ( no int NOT NULL, row1 numeric, constraint tbl_pkey PRIMARY KEY(no) ); postgres=# insert into tbl1 (no, row1) values ( 1, 99999999999999999999.999999999999999999999 ); INSERT 0 1 postgres=# select * from tbl1; 1 | 99999999999999999999.999999999999999999999
無指定でnumericと記述すると最大桁数格納できますが、numeric(p,s）で最大p桁、小数点第s位で丸め処理（s+1位で四捨五入）という指定になります。
postgres=# \d tbl20 no | integer | not null row20 | numeric(4,1) | -- 最大桁数（4桁）を超える数字をinsertしようとしたのでエラー postgres=# insert into tbl20 (no, row20) values ( 1, 99999999999999999999.999999999999999999999 ); ERROR: numeric field overflow DETAIL: A field with precision 4, scale 1 must round to an absolute value less than 10^3. -- 丸め処理で最大桁数（整数3桁＋小数1桁）を超えるため、同様のエラー postgres=# insert into tbl20 (no, row20) values ( 2, 999.999999999999999999999 ); ERROR: numeric field overflow DETAIL: A field with precision 4, scale 1 must round to an absolute value less than 10^3. -- 丸め処理により100.0がinsertされる postgres=# insert into tbl20 (no, row20) values ( 1, 99.999999999999999999999 ); INSERT 0 1 -- 丸め処理により 889.0がinsertされる postgres=# insert into tbl20 (no, row20) values ( 2, 888.999999999999999999999 ); INSERT 0 1 -- insertされた値の確認 postgres=# select * from tbl20; 1 | 100.0 2 | 889.0
また、decimal型というデータ型がありますが、これはNUMERIC型の別名と考えて良いでしょう。decimal型でテーブルをcreateするとNUMERIC型に変換されます。
postgres=# create table tbl2 ( no int NOT NULL, row2 decimal, constraint tbl2_pkey PRIMARY KEY(no) ); CREATE TABLE postgres=# \d tbl2 no | integer | not null row2 | numeric |
numericのフォーマット変換サンプル
numeric型は、to_char関数にフォーマット文字列を指定することでカンマ区切りやゼロパディング（0詰）が可能です。
-- カンマ区切り 指定桁数を超えると#表示になる
postgres=# select to_char(999999999999999999999999.99999999, '999,999,999,999,999,999,999,999.999' );
###,###,###,###,###,###,###,###.###
-- カンマ区切りで、小数点以下第三位を丸め処理する）
postgres=# select to_char(9999999999999999.99999999, '999,999,999,999,999,999,999,999.999' );
10,000,000,000,000,000.000
-- 丸め処理がおこらないケース
postgres=# select to_char(9999999999999999.999, '999,999,999,999,999,999,999,999.999' );
9,999,999,999,999,999.999
-- 整数部の0詰めをおこなう指定
postgres=# select to_char(9.99, 'FM9909.99' );
09.99
-- この指定だと整数部に0が表示されない
postgres=# select to_char(-0.1, '99.99');
-.10
-- 整数部の0が表示される
postgres=# select to_char(-0.1, '90.99');
-0.10
-- 小数点以下の0が表示されない
postgres=# select to_char(-0.1, 'FM9.99');
-.1
-- 数値の間に空白を入れる
postgres=# select to_char(485, '9 9 9');
4 8 5
参考）PostgreSQL公式ドキュメント データ型フォーマッティング関数
Numeric型のデータ変換でoverflowが起こるケース
numeric(4,1)は整数部＋小数部合わせた最大桁数が4、丸め処理をおこなって小数第1位までが有効になることを表しています。-999.9～999.9までの数値が扱えるんですね。
postgres=# select 111.4444::numeric(4,1); 111.4 postgres=# select 11111.4444::numeric(4,1); ERROR: numeric field overflow DETAIL: A field with precision 4, scale 1 must round to an absolute value less than 10^3.
範囲外の数値をinsertしようとすると、numeric field overflowエラーが出よくされます。なお、丸め処理をおこなった結果最大桁数を超えてしまった倍位にも、同様のエラーが出力されます。
まとめ
- NUMERICは大きな数値を扱えるデータ型。PostgreSQLやSQL Serverで使える。
- 特にPostgreSQLでは、10万桁を超える大きな桁数の数値の扱いが可能。
- NUMERIC型はto_char関数にフォーマット文字列を指定することでカンマ区切りや0パディングでの出力ができる。
