開発 PostgreSQL Oracle 備忘録 SQL

Oracle⇔Postgresqlの組み込みデータ型対応表

2015年6月20日

Oracle⇔Postgresqlの組み込みデータ型対応表を作成しました。

対象DB

  • Oracle 12c
  • Postgresql 9.4.3

型比較

項目が多いので各型へ飛べるリンクを記載します。

文字列型

OraclePostgres
型最大（範囲）型最大（範囲）
VARCHAR24,000バイト
（12g～）32,767バイト※1		character varying
(varchar)		1Gバイト※2
NVARCHAR24,000バイト
（12g～）32,767バイト※1		character varying
(varchar)		1Gバイト※2
CHAR4,000バイトcharacter
(char)		1Gバイト※2
NCHAR4,000バイトcharacter
(char)		1Gバイト※2
LONG2G-1バイトTEXT1Gバイト※4
CLOB(～9i）4G-1バイト
(10g～)128T-1バイト※3		TEXT1Gバイト※4
NCLOB(～9i）4G-1バイト
(10g～)128T-1バイト※3		TEXT1Gバイト※4
文字列型

フィールド作成時における型のサイズはOracle…バイト数指定又は文字数指定、Postgres…文字数指定なのに注意が必要
例）Oracleの場合…varchar(12)→12バイト、varchar(12 byte)→12バイト、varchar(12 char)→36バイト(UTF-8の場合）
　　※OracleのNVARCHAR・NCLOBはキャラクターセットで異なる（～8i…バイト数、9i～文字数）
　　Postgresの場合…varchar(12)→36バイト（UTF-8の場合）

※1 MAX_STRING_SIZE=EXTENDEDの場合。MAX_STRING_SIZE=STANDARDの場合は4,000バイトになります。EXTENDED設定後STANDARDに変更はできません。詳しくは拡張データ型にて

※2 フィールド作成時に型サイズを指定しない場合。サイズを指定する場合10,485,760バイトまで作成可能。

※3 DB_BLOCK_SIZEの値によって変動。デフォルト8192、2048～32768の設定が可能。データベース作成後変更不可。詳しくはDB_BLOCK_SIZEにて

※4 1GB以上のデータを格納する場合はラージオブジェクトを使用か外部ファイルに格納の検討が必要。

数値型

OraclePostgres
型最大（範囲）型最大（範囲）
NUMBER精度（10進数1～38）
位取り（10進数-84～127）		numeric
(decimal）		小数点前までは131,072桁
小数点以降は16,383桁
smallint
(int2)		-32,768～32,767
integer
(int,int4)		-2,147,483,648～2,147,483,647
bigint
(int8)		-9,223,372,036,854,775,808～9,223,372,036,854,775,807
real
(float4)		6桁精度
double precision
(float8)		15桁精度
FLOAT精度（2進数1～126）real※1
(float4)		6桁精度
double precision※1
(float8)		15桁精度
BINARY_FLOAT浮動小数点数(32ビット）real
(float4)		6桁精度
BINARY_DOUBLE浮動小数点数(64ビット）double precision
(float8)		15桁精度
なし smallserial※2
(serial4)		1～32767
なし serial※2
(serial4）		1～2147483647
なし bigserial※2
(serial8)		1～9223372036854775807
数値型

PostgresのNumericは計算速度が遅いため、大きな桁数が必要ない場合は他の型を扱うほうが良い。
但し、小数点以下のデータが存在する場合realやdouble precisionだと不正確なため正確な値が必要な場合はNumericを使用する。
Oracle、Postgres両方ともNumber(精度,位取り）で指定可能だが、位取りにマイナスが設定できるのはOracleのみなので注意が必要。

※1 floatで指定することも可能。float(1)～float(24)はreal、float（25）～float（53)をdouble precision、floatの場合はdouble precisionで設定される。

※2 Oracleに対応する型はないが、シーケンスと同義また、Postgresもシーケンス作成と同義になる。詳しくは8.1.4. 連番型にて

日付型

OraclePostgres
型最大（範囲）型最大（範囲）
DATE紀元前4712年1月1日～紀元(西暦)9999年12月31日
精度は秒まで		date紀元前4713年～紀元(西暦)5874897年※1
精度は日まで14桁
timestamp※3紀元前4713年～紀元(西暦)294276年
精度は1μ（マイクロ）秒まで14桁
TIMESTAMP紀元前4712年1月1日～紀元(西暦)9999年12月31日
精度は1n（ナノ）秒まで		timestamp※3紀元前4713年～紀元(西暦)294276年
精度は1μ（マイクロ）秒まで14桁
TIMESTAMP WITH TIMEZONE紀元前4712年1月1日～紀元(西暦)9999年12月31日
精度は1n（ナノ）秒まで
タイムゾーン情報付		timestamp with time zone※2 ※3紀元前4713年～紀元(西暦)294276年
精度は1μ（マイクロ）秒まで14桁
タイムゾーン情報付
TIMESTAMP WITH LOCAL TIMEZONE紀元前4712年1月1日～紀元(西暦)9999年12月31日
精度は1n（ナノ）秒まで
タイムゾーン情報付		timestamp with time zone※2 ※3紀元前4713年～紀元(西暦)294276年
精度は1μ（マイクロ）秒まで14桁
タイムゾーン情報付
INTERVAL YEAR TO MONTH0～9
デフォルトは2		interval※3-178000000年～178000000年
精度は1μ（マイクロ）秒まで14桁
INTERVAL DAY TO SECOND0～9
デフォルトは2		interval※3-178000000年～178000000年
精度は1μ（マイクロ）秒まで14桁
なし time※3時刻のみ
00:00:00～24:00:00
精度は1μ（マイクロ）秒まで14桁
なし time with time zone※3時刻のみ
00:00:00+1459～24:00:00-1459
精度は1μ（マイクロ）秒まで14桁
日付型

OracleとPostgresのtimestamp型については精度が異なる点注意が必要。
（Oracleは1×10-9秒に対しPostgresは1×10-6秒である。）

※1 Postgresのdate型は年月日までなので注意。時分秒が必要な場合はtimestamp型を使用する。
　　文字列→日付変換に使われるto_dateも年月日までなので時分秒が必要な場合はto_timestampを使用する。

※2 timestamptzと記述（省略）可能

※3 小数点以下の秒を入力する場合はフィールドを定義する際に0～6の値を入力。（time型は8バイト整数の場合6まで、浮動小数の場合は10まで）
　　例）timestamp(4)…0.0001秒単位で保存
　　timestampの値が8バイト整数で格納されている場合はμ秒精度まで有効、倍精度浮動小数点数の場合は精度落ちる場合あり。　

バイナリ型

OraclePostgres
型最大（範囲）型最大（範囲）
RAW2,000バイト
（12g～）32,767バイト※1		bytea1Gバイト※2
LONG RAW2Gバイトbytea1Gバイト※2
BLOB(～9i）4G-1バイト
(10g～)128T-1バイト※3		bytea1Gバイト※2
バイナリ型

※1 MAX_STRING_SIZE=EXTENDEDの場合。MAX_STRING_SIZE=STANDARDの場合は4,000バイトになります。EXTENDED設定後STANDARDに変更はできません。詳しくは拡張データ型にて

※2 1GB以上のデータを格納する場合はラージオブジェクトを使用か外部ファイルに格納の検討が必要。

※3 DB_BLOCK_SIZEの値によって変動。デフォルト8192、2048～32768の設定が可能。データベース作成後変更不可。詳しくはDB_BLOCK_SIZEにて

通貨型

OraclePostgres
型最大（範囲）型最大（範囲）
NUMBER精度（10進数1～38）
位取り（10進数-84～127）		money※1-92233720368547758.08～92233720368547758.07
小数点の精度はlc_monetaryの設定に依存
通貨型

※1 numeric型精度を落とすことなくにキャストすることが可能

論理値データ型

OraclePostgres
型最大（範囲）型最大（範囲）
VARCHAR24,000バイト
（12g～）32,767バイト※1		boolean真または偽※1
CHAR4,000バイト
論理値データ型

Oracleに論理値データ型は存在しないためVARCHAR2やCHARで代用する。
その際挿入リテラルの統一が必要。

※1 MAX_STRING_SIZE=EXTENDEDの場合。MAX_STRING_SIZE=STANDARDの場合は4,000バイトになります。EXTENDED設定後STANDARDに変更はできません。詳しくは拡張データ型にて

※2 "真"および"偽"のリテラルは以下の通り

真偽
TRUEFALSE
't''f'
'y''n'
'true''false'
'yes''no'
'on''off'
'1''0'
"真""偽"のリテラル表

列挙型

OraclePostgres
型最大（範囲）型最大（範囲）
VARCHAR24,000バイト
（12g～）32,767バイト※1 ※2		enum63バイト※3 ※4
CHAR4,000バイト※2
列挙型

JAVAやPHP等で使われる列挙型（enum）と似ています。

※1 Oracleでは存在しないためVARCHAR2やCHARで代用する。
　　enumで指定されたもの以外の文字列も挿入可能になるためプログラム側での制御が必要。

※2 MAX_STRING_SIZE=EXTENDEDの場合。MAX_STRING_SIZE=STANDARDの場合は4,000バイトになります。EXTENDED設定後STANDARDに変更はできません。詳しくは拡張データ型にて

※3 NAMEDATALENの値によって変更可能

※4 作成方法や使用方法はこちら

幾何データ型

OraclePostgres
型最大（範囲）型最大（範囲）
VARCHAR24,000バイト
（12g～）32,767バイト※1 ※2		point※3
line
lseg
box
path
polygon
circle
幾何データ型

※1 プログラム側にてデータ挿入・取得・演算等作成が必要。

※2 MAX_STRING_SIZE=EXTENDEDの場合。MAX_STRING_SIZE=STANDARDの場合は4,000バイトになります。EXTENDED設定後STANDARDに変更はできません。詳しくは拡張データ型にて

※3 使用方法や設定方法はこちら

ネットワークアドレス型

OraclePostgres
型最大（範囲）型最大（範囲）
VARCHAR24,000バイト
（12g～）32,767バイト※1 ※2		cidr※3
inet
macaddr
ネットワークアドレス型

※1 プログラム側にてデータ挿入・取得・演算等作成が必要。

※2 MAX_STRING_SIZE=EXTENDEDの場合。MAX_STRING_SIZE=STANDARDの場合は4,000バイトになります。EXTENDED設定後STANDARDに変更はできません。詳しくは拡張データ型にて

※3 使用方法や設定方法はこちら

ビット列データ型

OraclePostgres
型最大（範囲）型最大（範囲）
VARCHAR24,000バイト
（12g～）32,767バイト※1 ※2		bit※3
bit varying
ビット列データ型

※1 プログラム側にてデータ挿入・取得・演算等作成が必要。

※2 MAX_STRING_SIZE=EXTENDEDの場合。MAX_STRING_SIZE=STANDARDの場合は4,000バイトになります。EXTENDED設定後STANDARDに変更はできません。詳しくは拡張データ型にて

※3 使用方法や設定方法はこちら

その他

OraclePostgres
型最大（範囲）型最大（範囲）
BFILE4Gバイトなし 
ROWID 
UROWID 
なし oid32bit
その他

ROWIDはoidで置き換え可能だが、現在Postgresのほとんどのテーブルはデフォルトでoidが設定されないので別物として使用。
またoidは有限かつシステム内共通の値であるため、32bit（約43億）でオーバーフローしてしまい、システムがおかしくなる場合がある。
特に理由がない場合はoidのフィールドは作成しないほうがいいと思われる。

参考資料

-開発, PostgreSQL, Oracle, 備忘録, SQL
-, , , ,