PostgreSQL データ型
この章では、PostgreSQL のデータ型について説明します。データ型は、テーブル作成時に各フィールドに設定するものです。
データ型を設定する利点：
PostgreSQL は豊富なデータ型を提供しています。ユーザーは CREATE TYPE コマンドを使用してデータベースに新しいデータ型を作成できます。PostgreSQL のデータ型は非常に多くの種類があります。以下で具体的に説明します。
数値型
数値型は、2バイト、4バイト、または8バイトの整数と、4バイトまたは8バイトの浮動小数点数、および任意精度の10進数で構成されます。
次の表に、利用可能な数値型を示します。
| 名前 | 格納長 | 説明 | 範囲 |
|---|---|---|---|
| smallint | 2バイト | 小範囲整数 | -32768 から +32767 |
| integer | 4バイト | 一般的な整数 | -2147483648 から +2147483647 |
| bigint | 8バイト | 大範囲整数 | -9223372036854775808 から +9223372036854775807 |
| decimal | 可変長 | ユーザー指定の精度、正確 | 小数点前131072桁、小数点後16383桁 |
| numeric | 可変長 | ユーザー指定の精度、正確 | 小数点前131072桁、小数点後16383桁 |
| real | 4バイト | 可変精度、不正確 | 6桁の10進精度 |
| double precision | 8バイト | 可変精度、不正確 | 15桁の10進精度 |
| smallserial | 2バイト | 自動増分の小範囲整数 | 1 から 32767 |
| serial | 4バイト | 自動増分整数 | 1 から 2147483647 |
| bigserial | 8バイト | 自動増分の大範囲整数 | 1 から 9223372036854775807 |
通貨型
money 型は、固定小数精度の通貨額を格納します。
numeric、int、bigint 型の値は money に変換できます。浮動小数点数で通貨型を処理することは、丸め誤差が発生する可能性があるため推奨されません。
| 名前 | 格納容量 | 説明 | 範囲 |
|---|---|---|---|
| money | 8バイト | 通貨額 | -92233720368547758.08 から +92233720368547758.07 |
文字型
次の表に、PostgreSQL がサポートする文字型を示します。
| 番号 | 名前 & 説明 |
|---|---|
| 1 | character varying(n), varchar(n) 可変長、長さ制限あり |
| 2 | character(n), char(n) 固定長、不足分は空白で埋める |
| 3 | text 可変長、長さ制限なし |
日付/時刻型
次の表に、PostgreSQL がサポートする日付と時刻の型を示します。
| 名前 | 格納容量 | 説明 | 最小値 | 最大値 | 精度 |
|---|---|---|---|---|---|
| timestamp [ (p) ] [ without time zone ] | 8バイト | 日付と時刻（タイムゾーンなし） | 4713 BC | 294276 AD | 1 ミリ秒 / 14 桁 |
| timestamp [ (p) ] with time zone | 8バイト | 日付と時刻、タイムゾーンあり | 4713 BC | 294276 AD | 1 ミリ秒 / 14 桁 |
| date | 4バイト | 日付のみ | 4713 BC | 5874897 AD | 1 日 |
| time [ (p) ] [ without time zone ] | 8バイト | 時刻のみ | 00:00:00 | 24:00:00 | 1 ミリ秒 / 14 桁 |
| time [ (p) ] with time zone | 12バイト | 時刻のみ、タイムゾーン付き | 00:00:00+1459 | 24:00:00-1459 | 1 ミリ秒 / 14 桁 |
| interval [ fields ] [ (p) ] | 12バイト | 時間間隔 | -178000000 年 | 178000000 年 | 1 ミリ秒 / 14 桁 |
ブール型
PostgreSQL は標準の boolean データ型をサポートします。
boolean は "true"（真）または "false"（偽）の2つの状態を持ち、3番目の "unknown"（未知）状態は NULL で表されます。
| 名称 | 格納形式 | 説明 |
|---|---|---|
| boolean | 1バイト | true/false |
列挙型
列挙型は、静的な値の順序付き集合を含むデータ型です。
PostgreSQL の列挙型は、C 言語の enum 型に似ています。
他の型とは異なり、列挙型は CREATE TYPE コマンドを使用して作成する必要があります。
CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
1週間の曜日を作成するには、次のようにします。
CREATE TYPE week AS ENUM ('Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun');
他の型と同様に、列挙型は一度作成するとテーブルや関数の定義に使用できます。
CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
CREATE TABLE person (
name text,
current_mood mood
);
INSERT INTO person VALUES ('Moe', 'happy');
SELECT * FROM person WHERE current_mood = 'happy';
name | current_mood
------+--------------
Moe | happy
(1 row)
幾何型
幾何データ型は、2次元の平面オブジェクトを表します。
次の表に、PostgreSQL がサポートする幾何型を示します。
最も基本的な型は点です。これは他の型の基礎です。
| 名前 | 格納容量 | 説明 | 表現形式 |
|---|---|---|---|
| point | 16バイト | 平面内の点 | (x,y) |
| line | 32バイト | （無限）直線（未完全実装） | ((x1,y1),(x2,y2)) |
| lseg | 32バイト | （有限）線分 | ((x1,y1),(x2,y2)) |
| box | 32バイト | 矩形 | ((x1,y1),(x2,y2)) |
| path | 16+16n バイト | 閉じた経路（多角形に類似） | ((x1,y1),...) |
| path | 16+16n バイト | 開いた経路 | [(x1,y1),...] |
| polygon | 40+16n バイト | 多角形（閉じた経路に類似） | ((x1,y1),...) |
| circle | 24バイト | 圆 | <(x,y),r> （中心と半径） |
ネットワークアドレス型
PostgreSQL は、IPv4、IPv6、MAC アドレスを格納するためのデータ型を提供します。
これらのデータ型を使用してネットワークアドレスを格納することは、プレーンテキスト型を使用するよりも優れています。これらの型は入力エラーのチェックと特別な操作や機能を提供するからです。
| 名前 | 格納容量 | 説明 |
|---|---|---|
| cidr | 7 または 19 バイト | IPv4 または IPv6 ネットワーク |
| inet | 7 または 19 バイト | IPv4 または IPv6 ホストとネットワーク |
| macaddr | 6バイト | MAC アドレス |
inet または cidr データ型を並べ替える場合、IPv4 アドレスは常に IPv6 アドレスの前に並びます。これには、::10.2.3.4 や ::ffff:10.4.3.2 のように、IPv6 アドレスにカプセル化またはマッピングされた IPv4 アドレスも含まれます。
ビット列型
ビット列は、1 と 0 の文字列です。ビットマスクの格納や可視化に使用できます。SQL ビット型には、bit(n) と bit varying(n) の2種類があります。ここで n は正の整数です。
bit 型のデータは長さ n に正確に一致しなければなりません。短すぎたり長すぎたりするデータを保存しようとするとエラーになります。bit varying 型は最大長 n の可変長型です。それより長い文字列は拒否されます。長さを指定しない bit は bit(1) と同じであり、長さを指定しない bit varying は長さ制限なしという意味です。
テキスト検索型
全文検索とは、自然言語ドキュメントの集合から、クエリに一致するものを探し出す検索です。
PostgreSQL は全文検索をサポートするために2つのデータ型を提供しています。
| 番号 | 名前 & 説明 |
|---|---|
| 1 | tsvector tsvector の値は、重複のない lexemes のソート済みリストです。つまり、同じ単語の異なる変形を標準化したものです。 |
| 2 | tsquery tsquery は検索用の語彙を保存し、ブール演算子 &(AND)、|(OR)、!(NOT) を使ってそれらを組み合わせます。括弧は演算子のグループ化を明示するために使われます。 |
UUID 型
uuid データ型は、RFC 4122、ISO/IEF 9834-8:2005 および関連標準で定義されている汎用一意識別子（UUID）を格納するために使われます。（一部のシステムでは、このデータ型はグローバル一意識別子、または GUID と呼ばれています。）この識別子はアルゴリズムによって生成される128ビットの識別子であり、同じアルゴリズムを使用する既知のモジュール内で、他の方法で生成された識別子と同一になることは事実上不可能です。したがって、分散システムにとって、この識別子はシーケンスよりも一意性の保証に優れています。なぜなら、シーケンスは単一データベース内でのみ一意性を保証できるからです。
UUID は、小文字の16進数字の並びとして記述され、ハイフンでいくつかのグループに分割されます。具体的には、8桁のグループ1つ + 4桁のグループ3つ + 12桁のグループ1つで、合計32桁が128ビットを表します。この標準的な UUID の例は次のとおりです。
a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11
XML 型
xml データ型は XML データを格納するために使用できます。XML データを text 型に格納する場合と比べた利点は、入力値が整形式であるかどうかを検査でき、さらに、関数による型安全性の検査をサポートすることです。このデータ型を使用するには、コンパイル時に ... を使用しなければなりません。configure --with-libxml。
xml は、XML 標準で定義された整形式の「ドキュメント」、および XML 標準のXMLDecl? contentで定義される「コンテンツ」フラグメントを格納できます。大まかに言うと、コンテンツフラグメントは複数のトップレベル要素や文字ノードを持つことができるということです。xmlvalue IS DOCUMENT 式を使用すると、特定の xml 値が完全なドキュメントなのかコンテンツフラグメントなのかを判断できます。
XML値の作成
関数 xmlparse: を使用して、文字データから xml 型の値を生成します。
XMLPARSE (DOCUMENT '<?xml version="1.0"?><book><title>Manual</title><chapter>...</chapter></book>') XMLPARSE (CONTENT 'abc<foo>bar</foo><bar>foo</bar>')
JSON 型
json データ型は JSON（JavaScript Object Notation）データを格納するために使用できます。そのようなデータは text として格納することもできますが、json データ型は、格納される各値が有効な JSON 値であることを検査するのに適しています。
さらに、json データを処理するための関連関数があります。
| 例 | 例の結果 |
|---|---|
| array_to_json('{{1,5},{99,100}}'::int[]) | [[1,5],[99,100]] |
| row_to_json(row(1,'foo')) | {"f1":1,"f2":"foo"} |
配列型
PostgreSQL では、フィールドを可変長の多次元配列として定義できます。
配列型は、任意の基本型、ユーザー定義型、列挙型、または複合型にすることができます。
配列の宣言
テーブルを作成するときに、配列を次のように宣言できます。
CREATE TABLE sal_emp (
name text,
pay_by_quarter integer[],
schedule text[][]
);
pay_by_quarter は1次元の整数配列、schedule は2次元のテキスト型配列です。
"ARRAY" キーワードを使用することもできます。次のとおりです。
CREATE TABLE sal_emp ( name text, pay_by_quarter integer ARRAY[4], schedule text[][] );
値の挿入
値の挿入には波括弧 {} を使用し、要素は {} 内でカンマで区切ります。
INSERT INTO sal_emp
VALUES ('Bill',
'{10000, 10000, 10000, 10000}',
'{{"meeting", "lunch"}, {"training", "presentation"}}');
INSERT INTO sal_emp
VALUES ('Carol',
'{20000, 25000, 25000, 25000}',
'{{"breakfast", "consulting"}, {"meeting", "lunch"}}');
配列へのアクセス
次に、このテーブルに対していくつかのクエリを実行できます。
まず、配列の1つの要素にアクセスする方法を示します。このクエリは、第2四半期に給与が変わった従業員の名前を取得します。
SELECT name FROM sal_emp WHERE pay_by_quarter[1] <> pay_by_quarter[2]; name ------- Carol (1 row)
配列の添字は角括弧内に記述します。
配列の変更
配列の値を変更できます。
UPDATE sal_emp SET pay_by_quarter = '{25000,25000,27000,27000}'
WHERE name = 'Carol';
または、ARRAY コンストラクタ構文を使用します。
UPDATE sal_emp SET pay_by_quarter = ARRAY[25000,25000,27000,27000]
WHERE name = 'Carol';
配列内の検索
配列内の値を検索するには、その配列のすべての値を検査する必要があります。
例えば：
SELECT * FROM sal_emp WHERE pay_by_quarter[1] = 10000 OR
pay_by_quarter[2] = 10000 OR
pay_by_quarter[3] = 10000 OR
pay_by_quarter[4] = 10000;
あるいは、次の文を使用して、配列内のすべての要素の値が 10000 に等しい行を検索できます。
SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter);
または、generate_subscripts 関数を使用できます。例：
SELECT * FROM
(SELECT pay_by_quarter,
generate_subscripts(pay_by_quarter, 1) AS s
FROM sal_emp) AS foo
WHERE pay_by_quarter[s] = 10000;
複合型
複合型は、1行または1レコードの構造を表します。実際には、フィールド名とそのデータ型のリストにすぎません。PostgreSQL では、単純なデータ型と同様に複合型を使用できます。たとえば、テーブルのあるフィールドを複合型として宣言できます。
複合型の宣言
以下は、複合型を定義する2つの簡単な例です。
CREATE TYPE complex AS (
r double precision,
i double precision
);
CREATE TYPE inventory_item AS (
name text,
supplier_id integer,
price numeric
);
構文は CREATE TABLE に似ていますが、ここではフィールド名と型のみを宣言できます。
型を定義すると、それを使ってテーブルを作成できます。
CREATE TABLE on_hand (
item inventory_item,
count integer
);
INSERT INTO on_hand VALUES (ROW('fuzzy dice', 42, 1.99), 1000);
複合型の値の入力
複合型の値をテキスト定数として記述するには、フィールド値を丸括弧で囲み、カンマで区切ります。任意のフィールド値の周りに二重引用符を付けることができます。値自体にカンマや丸括弧が含まれる場合は、二重引用符で囲む必要があります。
複合型定数の一般的な書式は次のとおりです。
'( val1 , val2 , ... )'
例は次のとおりです。
'("fuzzy dice",42,1.99)'
複合型へのアクセス
複合型フィールドの1つのフィールドにアクセスするには、ドットとそのフィールド名を記述します。これはテーブル名からフィールドを選択するのと非常に似ています。実際、テーブル名からフィールドを選択するのとあまりに似ているため、パーサーが混乱しないように丸括弧を使う必要がよくあります。たとえば、on_hand の例のテーブルからいくつかのサブフィールドを選択する場合は、次のようにします。
SELECT item.name FROM on_hand WHERE item.price > 9.99;
これは機能しません。SQL 構文では、item はテーブル名から選択されるものであり、フィールド名から選択されるものではないからです。次のように記述する必要があります。
SELECT (item).name FROM on_hand WHERE (item).price > 9.99;
あるいは、テーブル名も使用する必要がある場合（たとえば、複数テーブルのクエリ内）は、次のように記述します。
SELECT (on_hand.item).name FROM on_hand WHERE (on_hand.item).price > 9.99;
これで、丸括弧で囲まれたオブジェクトは item フィールドを指す参照として正しく解析され、そこからサブフィールドを選択できます。
範囲型
範囲データ型は、特定の要素型の一定範囲内の値を表します。
たとえば、timestamp の範囲は、会議室が予約されている時間範囲を表すために使用できます。
PostgreSQL に組み込まれている範囲型は次のとおりです。
int4range — integer の範囲
int8range — bigint の範囲
numrange — numeric の範囲
tsrange — timestamp without time zone の範囲
tstzrange — timestamp with time zone の範囲
daterange — date の範囲
さらに、独自の範囲型を定義できます。
CREATE TABLE reservation (room int, during tsrange);
INSERT INTO reservation VALUES
(1108, '[2010-01-01 14:30, 2010-01-01 15:30)');
-- 包含
SELECT int4range(10, 20) @> 3;
-- 重叠
SELECT numrange(11.1, 22.2) && numrange(20.0, 30.0);
-- 提取上边界
SELECT upper(int8range(15, 25));
-- 计算交叉
SELECT int4range(10, 20) * int4range(15, 25);
-- 范围是否为空
SELECT isempty(numrange(1, 5));
範囲値の入力は、次の書式に従う必要があります。
(下界,上界) (下界,上界] [下界,上界) [下界,上界] 空集合
丸括弧または角括弧は、下界と上界が含まれないか含まれるかを示します。最後の書式は空を表し、空の範囲（値を含まない範囲）を意味することに注意してください。
-- 包括3，不包括7，并且包括二者之间的所有点 SELECT '[3,7)'::int4range; -- 不包括3和7，但是包括二者之间所有点 SELECT '(3,7)'::int4range; -- 只包括单一值4 SELECT '[4,4]'::int4range; -- 不包括点（被标准化为‘空’） SELECT '[4,4)'::int4range;
オブジェクト識別子型
PostgreSQL は内部でオブジェクト識別子（OID）をさまざまなシステムテーブルの主キーとして使用します。
また、システムはユーザーが作成したテーブルに OID システムフィールドを追加しません（テーブル作成時に WITH OIDS を宣言した場合、または設定パラメータ default_with_oids が有効になっている場合を除く）。oid 型はオブジェクト識別子を表します。この他に、oid にはいくつかの別名があります： regproc, regprocedure, regoper, regoperator, regclass, regtype, regconfig, および regdictionary。
| 名前 | 参照 | 説明 | 数値例 |
|---|---|---|---|
| oid | 任意 | 数値化されたオブジェクト識別子 | 564182 |
| regproc | pg_proc | 関数名 | sum |
| regprocedure | pg_proc | 引数型を伴う関数 | sum(int4) |
| regoper | pg_operator | 演算子名 | + |
| regoperator | pg_operator | 引数型を伴う演算子 | *(integer,integer) または -(NONE,integer) |
| regclass | pg_class | リレーション名 | pg_type |
| regtype | pg_type | データ型名 | integer |
| regconfig | pg_ts_config | テキスト検索設定 | english |
| regdictionary | pg_ts_dict | テキスト検索辞書 | simple |
疑似型
PostgreSQL の型システムには、特別な用途を持つ一連の項目が含まれており、これらはカテゴリとして疑似型と呼ばれます。疑似型はフィールドのデータ型として使用することはできませんが、関数の引数または結果型を宣言するために使用できます。疑似型は、関数が単に特定の SQL データ型を受け取って返すだけではない場合に役立ちます。
次の表に、すべての疑似型を示します。
| 名前 | 説明 |
|---|---|
| any | 関数が任意の入力データ型を受け付けることを示します。 |
| anyelement | 関数が任意のデータ型を受け付けることを示します。 |
| anyarray | 関数が任意の配列データ型を受け付けることを示します。 |
| anynonarray | 関数が任意の非配列データ型を受け付けることを示します。 |
| anyenum | 関数が任意の列挙データ型を受け付けることを示します。 |
| anyrange | 関数が任意の範囲データ型を受け付けることを示します。 |
| cstring | 関数がヌル終端のC文字列を受け入れるか返すことを示します。 |
| internal | 関数がサーバー内部のデータ型を受け入れるか返すことを示します。 |
| language_handler | プロシージャ言語呼び出しハンドラはlanguage_handlerを返すように宣言されます。 |
| fdw_handler | 外部データラッパーはfdw_handlerを返すように宣言されます。 |
| record | 関数が未宣言の行型を返すことを識別します。 |
| trigger | トリガー関数はtriggerを返すように宣言されます。 |
| void | 関数が値を返さないことを示します。 |
| opaque | 以前は上記のすべての目的で使用されていた廃止された型です。 |
その他の拡張詳細は以下を参照してください：PostgreSQLデータ型