基本的なデータ型とその使い方については、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).
FLOAT
, REAL
, DOUBLE PRECISION
DATE
ROW
行型 -- 表の列の並びとそれぞれの型. 実体はそれぞれの行。
REF
参照型
ARRAY
配列型
MULTISET
マルチ集合型
RDBMSごとのSQLの差異については、Comparison of different SQL implementations が詳しい。(2006.7.24追加。) SQL:2008, PostgreSQL, DB2, MS SQL Server, MySQL, Oracle, Infomix.
配列なども組み込みなので、ほかのプログラミング言語の用語だと primitive.
データベースに格納される文字は, 何らかの文字集合 (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 |
BINARY
型は, 文字集合も何らかの照合順も持たない. バイト (オクテット) 単位で, 値0や文字集合にない値を格納できる。値は相互に比較可能。
PostgreSQL の BYTEA
は, 1Gバイトまで格納できる。これを超える場合は oid型 + ラージオブジェクト機能を使う。
標準SQL | MySQL | PostgreSQL | |||
---|---|---|---|---|---|
正式名 | 別名 | 正式名 | 別名 | 正式名 | 別名 |
BINARY
| |||||
BINARY VARYING
| |||||
BINARY LARGE OBJECT *1 |
BLOB *1 | BLOB | BYTEA | ||
TINYLOB | |||||
MEDIUMBLOB | |||||
LONGBLOB |
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] |
値として TRUE
または FALSE
を取る。NOT NULL
制約によって禁止されない限り, 不定としてナル値も取ることがある。
標準SQL | MySQL | PostgreSQL | |||
---|---|---|---|---|---|
正式名 | 別名 | 正式名 | 別名 | 正式名 | 別名 |
BOOLEAN *1 |
BOOL | BOOLEAN | BOOL |
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 (長さ) |
列の値として配列を格納できるようになった。配列の要素、個々の値に制約を付けたり、多対多にすることがなければ、軽い感じで作ることができる。
正規化への挑戦。第一正規形を壊す。
例えば, 次のようにする。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,
MULTISET
順序付けられていない集まり。
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 |