SQL 開発 PostgreSQL Oracle 備忘録

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

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

対象DB

  • Oracle 12c
  • Postgresql 9.4.3

型比較

文字列型数値型日付型バイナリ型通貨型(Postgresのみ) ・論理値データ型(Postgresのみ) ・列挙型(Postgresのみ) ・幾何データ型(Postgresのみ) ・ネットワークアドレス型(Postgresのみ) ・ビット列データ型(Postgresのみ) ・その他

文字列型

Oracle Postgres
最大(範囲) 最大(範囲)
VARCHAR2 4,000バイト
(12g~)32,767バイト※1

character varying
(varchar)
1Gバイト※2
NVARCHAR2 4,000バイト
(12g~)32,767バイト※1
character varying
(varchar)
1Gバイト※2
CHAR 4,000バイト character
(char)
1Gバイト※2
NCHAR 4,000バイト character
(char)
1Gバイト※2
LONG

2G-1バイト TEXT 1Gバイト※4
CLOB

(~9i)4G-1バイト
(10g~)128T-1バイト※3
TEXT 1Gバイト※4
NCLOB (~9i)4G-1バイト
(10g~)128T-1バイト※3
TEXT 1Gバイト※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以上のデータを格納する場合はラージオブジェクトを使用か外部ファイルに格納の検討が必要。

数値型

Oracle Postgres
最大(範囲) 最大(範囲)
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. 連番型にて

日付型

Oracle Postgres
最大(範囲) 最大(範囲)
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 MONTH 0~9
デフォルトは2
interval※3 -178000000年~178000000年
精度は1μ(マイクロ)秒まで14桁
INTERVAL DAY TO SECOND 0~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桁
< p id="date_1">OracleとPostgresのtimestamp型については精度が異なる。
(Oracleは1×10-9秒に対しPostgresは1×10-6秒である。)
< p id="date_1">※1 Postgresのdate型は年月日までなので注意。時分秒が必要な場合はtimestamp型を使用する。
文字列→日付変換に使われるto_dateも年月日までなので時分秒が必要な場合はto_timestampを使用する。 < p id="date_2">※2 timestamptzと記述(省略)可能

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

バイナリ型

Oracle Postgres
最大(範囲) 最大(範囲)
RAW 2,000バイト
(12g~)32,767バイト※1
bytea 1Gバイト※2

LONG RAW 2Gバイト bytea 1Gバイト※2
BLOB (~9i)4G-1バイト
(10g~)128T-1バイト※3
bytea 1Gバイト※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にて

通貨型

Oracle Postgres
最大(範囲) 最大(範囲)
NUMBER 精度(10進数1~38)
位取り(10進数-84~127)
money※1 -92233720368547758.08~92233720368547758.07
小数点の精度はlc_monetaryの設定に依存

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

論理値データ型

Oracle Postgres
最大(範囲) 最大(範囲)
VARCHAR2 4,000バイト
(12g~)32,767バイト※1

boolean 真または偽※1
CHAR 4,000バイト
Oracleに論理値データ型は存在しないためVARCHAR2やCHARで代用する。
その際挿入リテラルの統一が必要。 ※1 MAX_STRING_SIZE=EXTENDEDの場合。MAX_STRING_SIZE=STANDARDの場合は4,000バイトになります。EXTENDED設定後STANDARDに変更はできません。詳しくは拡張データ型にて

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

TRUE FALSE
't' 'f'
'y' 'n'
'true' 'false'
'yes' 'no'
'on' 'off'
'1' '0'

列挙型

Oracle Postgres
最大(範囲) 最大(範囲)
VARCHAR2 4,000バイト
(12g~)32,767バイト※1 ※2

enum 63バイト※3 ※4
CHAR 4,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 作成方法や使用方法はこちら

幾何データ型

Oracle Postgres
最大(範囲) 最大(範囲)
VARCHAR2 4,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 使用方法や設定方法はこちら

ネットワークアドレス型

Oracle Postgres
最大(範囲) 最大(範囲)
VARCHAR2 4,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 使用方法や設定方法はこちら

ビット列データ型

Oracle Postgres
最大(範囲) 最大(範囲)
VARCHAR2 4,000バイト
(12g~)32,767バイト※1 ※2

cidr ※3
inet
macaddr
< p id="bit_1">※1 プログラム側にてデータ挿入・取得・演算等作成が必要。

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

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

 

その他

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

参考資料

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