追補: SQLデータ型の一覧

基本的なデータ型とその使い方については、p.45からp.48をご覧ください。

*1 はSQL99で標準化されたものです。[ ] は省略可能です。

(2017.8.24) 更新。

最新の標準SQL は ISO/IEC 9075-2:2011 SQL/Foundation (JIS X 3005-2:2015). 一つ前が ISO/IEC 9075-2:2008 (JIS X 3005-2:2010).

  • 組込みデータ型 (built-in data type)
  • 構成型 (constructed type)
    • 集まり型 (collection type)
      • ARRAY 配列型
      • MULTISET マルチ集合型
    • REF 参照型
    • ROW 行型
  • 利用者定義型 (user-defined type)
    • 個別型 (distinct type)
    • 構造型 (structured type)

RDBMSごとのSQLの差異については、Comparison of different SQL implementations が詳しい。(2006.7.24追加。)

組み込みデータ型

配列なども組み込みなので、ほかのプログラミング言語の用語だと primitive.

数値

MySQLでは符号なし整数 int unsigned をよく使うが、PostgreSQLにはない。

MySQLでは主キーの値を自動生成するためにauto_incrementを付加するが、PostgreSQLではserial型を使う。

標準SQLでは, NUMERIC, DECIMAL (DEC) は、精度を指定できる。SMALLINT, INTEGER (INT), BIGINT は精度を指定できない。

NUMERIC および DECIMAL は 10進数。NUMERIC は厳密に指定した精度になり、DECIMAL は指定した精度以上の精度となる。(-- 構文上、精度を省略できるけど, その場合は?) ただし、実装では, 演算は INT などに比べると遅くなる。

精度は、小数点を挟んだ両側の桁数の合計。位取りは小数点以下の桁数。'12.3456' を格納するには, 精度6と位取り4が必要。

標準SQL MySQL PostgreSQL
正式名 別名 正式名 別名 正式名 別名
SMALLINT SMALLINT [(精度)] [UNSIGNED] [ZEROFILL] SMALLINT INT2
INTEGER INT INTEGER [(精度)] [UNSIGNED] [ZEROFILL] INT [(精度)] [UNSIGNED] [ZEROFILL] INTEGER INT, INT4
DECIMAL [(精度 [, 位取り])] DEC [(精度 [, 位取り])] DECIMAL [(精度 [, 位取り])] [UNSIGNED] [ZEROFILL] DEC [(精度 [, 位取り])] [UNSIGNED] [ZEROFILL] DECIMAL [(精度, 位取り)]
NUMERIC [(精度 [, 位取り])] NUMERIC [(精度 [, 位取り])] [UNSIGNED] [ZEROFILL] NUMERIC [(精度, 位取り)]
BIGINT BIGINT [(長さ)] [UNSIGNED] [ZEROFILL] BIGINT INT8

標準SQLでは, FLOAT は精度を指定できるが、REAL, DOUBLE PRECISION はできない。

FLOATの精度は2進数の桁数. PostgreSQL では, FLOAT(24) までは REAL になり, FLOAT(53) までは DOUBLE PRECISION になる。超えるとエラーになる。

標準SQL MySQL PostgreSQL
正式名 別名 正式名 別名 正式名 別名
FLOAT [(精度)] FLOAT (精度) [UNSIGNED] [ZEROFILL] FLOAT [(精度, 位取り)] [UNSIGNED] [ZEROFILL]
DOUBLE PRECISION DOUBLE PRECISION [(精度, 位取り)] [UNSIGNED] [ZEROFILL] DOUBLE PRECISION FLOAT8
REAL REAL [(精度, 位取り)] [UNSIGNED] [ZEROFILL] DOUBLE [(精度, 位取り)] [UNSIGNED] [ZEROFILL] REAL FLOAT4
TINYINT [(長さ)] [UNSIGNED] [ZEROFILL]
MEDIUMINT [(長さ)] [UNSIGNED] [ZEROFILL]

文字列

データベースに格納される文字は, 何らかの文字集合 (Coded Character Set; CCS) またはその組み合わせのなかの値になる。文字の値として 0 を格納することはできない。文字集合にない値も格納できない。そういうときはバイナリ型を使う。

文字集合を指定するところでは, 実際には入出力の形式が必要なので, UTF-8 のような文字コード (Character Encoding Scheme; CES) で指定する。

NATIONAL CHARACTER (NCHAR) は、処理系定義の文字集合。現代では CHAR も"文字"単位なので、N付きはもはや使わない。

文字集合として Unicode を使う場合, 標準SQLでは正規化をデータベース側でやってくれそうな感じだが、PostgreSQL はそのようになっていない。NORMALIZE 関数や NORMALIZED 述語はない。プログラム側で正規化してから投入すること。

PostgreSQL では, TEXT 型は 1Gバイトぐらいまでの文字列を格納できる。これを超える場合は oid型 + ラージオブジェクト機能を使う。

標準SQL MySQL PostgreSQL
正式名 別名 正式名 別名 正式名 別名
CHARACTER [(長さ)] CHAR [(長さ)] CHAR [(長さ)] [BINARY] CHARACTER (長さ) CHAR (長さ)
NATIONAL CHARACTER [(長さ)] NATIONAL CHAR [(長さ)], NCHAR [(長さ)] NATIONAL CHAR (長さ) [BINARY]
CHARACTER VARYING (長さ) CHAR VARYING (長さ),VARCHAR (長さ) VARCHAR (長さ) [BINARY] CHARACTER VARYING (長さ) VARCHAR (長さ)
NATIONAL CHARACTER VARYING (長さ) NATIONAL CHAR VARYING (長さ), NCHAR VARYING (長さ) NATIONAL VARCHAR (長さ) [BINARY]
CHARACTER LARGE OBJECT *1 CLOB *1 TEXT TEXT
TINYTEXT
MEDIUMTEXT
LONGTEXT

日時データ型と, 時間隔データ型

WITH TIME ZONE の存在が話をヤヤコシクしている。

単に TIMESTAMP, TIME としたときは, WITHOUT TIME ZONE (時刻帯なし) の意味になる。

WITH TIME ZONE の時刻は, 表に出てくるのは地方時のほう。EXTRACT関数でも、"時"フィールドは内部のUTCでの時刻ではなく, タイムゾーンを加味した時刻のほうが得られる。

WITH TIME ZONE の時刻 [A] と WITHOUT TIME ZONE の時刻 [B] を比較すると, WITHOUT TIME ZONE の本当のタイムゾーンの意図が何であれ, [A] の時刻にそのタイムゾーンの時差を加減算した地方時の (時, 分, 秒) 部分と, [B] の時刻が比較される。

標準SQL MySQL PostgreSQL
正式名 別名 正式名 別名 正式名 別名
DATE DATE DATE
TIMESTAMP [(精度)] [WITH TIME ZONE] TIMESTAMP [(精度)] TIMESTAMP [(精度)] [WITH TIME ZONE] TIMESTAMPTZ
TIME [(精度)] [WITH TIME ZONE] TIME TIME [(精度)] WITH TIME ZONE TIMETZ
DATETIME
YEAR [(2 | 4)]
TIMESTAMP [(精度)] WITHOUT TIME ZONE TIMESTAMP
TIME [(精度)] [WITHOUT TIME ZONE]
標準SQL MySQL PostgreSQL
正式名 別名 正式名 別名 正式名 別名
INTERVAL 時間隔修飾子 INTERVAL (精度)

バイナリ (binary) 型

BINARY型は文字との対比になっている。バイト (オクテット) 単位で, 値0や文字集合にない値を格納できる。

PostgreSQL の BYTEA は, 1Gバイトまで格納できる。これを超える場合は oid型 + ラージオブジェクト機能を使う。

標準SQL MySQL PostgreSQL
正式名 別名 正式名 別名 正式名 別名
BINARY
BINARY VARYING
BINARY LARGE OBJECT *1 BLOB *1 BLOB BYTEA
TINYLOB
MEDIUMBLOB
LONGBLOB

ビット列

ビット列は, 標準SQLでは, SQL:2003で取り除かれた。

標準SQL MySQL PostgreSQL
正式名 別名 正式名 別名 正式名 別名
BIT [(長さ)] BIT BIT
BIT VARYING (長さ) BIT VARYING (長さ) VARBIT (長さ)

ブール型

値として TRUE または FALSE を取る。NOT NULL制約によって禁止されない限り, 不定としてナル値も取ることがある。

標準SQL MySQL PostgreSQL
正式名 別名 正式名 別名 正式名 別名
BOOLEAN *1 BOOL BOOLEAN BOOL

その他

標準SQL MySQL PostgreSQL
正式名 別名 正式名 別名 正式名 別名
ENUM ('値1', '値2', ...)
SET ('値1', '値2', ...)
BOX
CIDR
CIRCLE
INET
LINE
LSEG
MACADDR
MONEY
PATH
POINT
POLYGON
SERIAL SERIAL4
BIGSERIAL SERIAL8

利用者定義型

●4.7

行型

●4.8

参照型

●4.9

集まり型

●4.10

配列型

列の値として配列を格納できるようになった。配列の要素、個々の値に制約を付けたり、多対多にすることがなければ、軽い感じで作ることができる。

例えば, 次のようにする。ARRAY キーワードを使う。

CREATE TABLE connected_apps (
  id            serial PRIMARY KEY,
  client_key    VARCHAR(64) NOT NULL UNIQUE,
  
  name          VARCHAR(100) NOT NULL, -- 人間用の名前

  client_secret VARCHAR(64) NOT NULL,
  redirect_uris VARCHAR(200) ARRAY[3],
  
  created_at TIMESTAMP NOT NULL,
  updated_at TIMESTAMP
);

配列の要素の数に制約を付けないなら, 次のようにする。ARRAY[] ではない。

  redirect_uris VARCHAR(200) ARRAY,

マルチ集合