追補: 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).

  • 事前定義されたデータ型 (組込みデータ型)
    1. 文字列型 (character string type), 長大オブジェクトデータ列型あり.
    2. 2進オクテット列 (binary string) 型, 長大オブジェクトデータ列型あり.
    3. 数型
      • 真数型
      • 概数型 - FLOAT, REAL, DOUBLE PRECISION
    4. ブール型 (Boolean types)
    5. 日時型および時間隔型
      • 時刻型
      • DATE
      • 時間隔型
  • 構成型 (constructed type)
    1. ROW 行型 -- 表の列の並びとそれぞれの型. 実体はそれぞれの行。
    2. REF 参照型
    3. 集まり型 (collection type)
      1. ARRAY 配列型
      2. MULTISET マルチ集合型
      3. その他の拡張
  • 利用者定義型 (user-defined type)
    • 個別型 (distinct type)
    • 構造型 (structured type)

RDBMSごとのSQLの差異については、Comparison of different SQL implementations が詳しい。(2006.7.24追加。) SQL:2008, PostgreSQL, DB2, MS SQL Server, MySQL, Oracle, Infomix.

組込みデータ型

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

1. 文字列

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

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

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

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

[2020.11] PostgreSQL 12 でも同様。

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

2. バイナリ (binary string) 型

BINARY型は, 文字集合も何らかの照合順も持たない. バイト (オクテット) 単位で, 値0や文字集合にない値を格納できる。値は相互に比較可能。

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

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

3. 数値

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]

4. ブール型

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

次の記事は「PostgreSQLはこれをNULLで代用するという明らかに間違った独自仕様を採用しています」とあるが、言い掛かり。標準 SQL は明確にナル値と不定とを区別せず、交換可能としてよい、と定めている。第8回 SQLにおける論理演算 ～なぜ真理を隠すのか～ (1）各DBの真理値型のサポート

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

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

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 (精度)

廃止: ビット列

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

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

集まり (collection) 型

1. 配列型

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

正規化への挑戦。第一正規形を壊す。

例えば, 次のようにする。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,

2. マルチ集合

MULTISET 順序付けられていない集まり。

3. その他の拡張

DBMS の拡張。

PostgreSQL には, JSON 型, XML 型などがある。

JSON対応は, ISO/IEC TR 19075-6 SQL Technical Reports - Part 6: SQL support for JavaScript Object Notation (JSON) として標準化。PostgreSQL 12 の JSON は, RFC 7159 (2014年3月) によっている。最新は RFC 8259 = ECMA-404 2nd Edition (2017年12月; 厳密に一致) になっており、一つ前の仕様を参照。

XML対応は, ISO/IEC 9075-14, SQL - Part 14: XML-Related Specifications (SQL/XML) として標準化されている。PostgreSQL ではリテラルを拡張するなど、独自性がある。XQuery のサブセットである XPath式で検索できる。

その他

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