この章では、PostgreSQLで使用するデータ型に関して説明します。 テーブルを作成する際、各カラムに対してデータ型を指定します。
-
整合性 – 同じデータ型の列に対する操作は一貫した結果をもたらし、通常最も高速になります。
-
検証 – データ型を適切に使用することで、データのフォーマット検証を行い、データ型の範囲外のデータを拒否することができます。
-
コンパクト性 – 列には1つの型の値を格納できるため、コンパクトに格納できます。
PostgreSQLは幅広いデータ型のセットをサポートしています。 さらに、ユーザはCREATE TYPE SQLコマンドを使用して、独自のデータ型を作成することができます。 PostgreSQLには異なるデータ型のカテゴリがあります。
数値型
数値型は2バイト、4バイト、8バイトの整数、4バイト、8バイトの浮動小数点数、選択可能な精度の小数で構成されています。 次の表は、利用可能な型を示したものです。
| 名前 | 格納サイズ | 説明 | 範囲 |
|---|---|---|---|
| smallint | 2 bytes | small- | -32768 to +32767 |
| integer | 4 bytes | typical choice for integer | -の典型的な選択肢。2147483648～+2147483647 |
| bigint | 大域整数 | -…9223372036854775808〜9223372036854775807 | |
| decimal | variable | ユーザ指定精度。exact | 小数点以下の桁数は131072桁まで。 小数点以下16383桁まで |
| numeric | 変数 | ユーザ指定精度、正確 | 小数点以下131072桁まで。 小数点以下16383桁まで |
| real | 4 bytes | variable-precision,inexact | 6 decimal digits precision |
| double precision | 8 bytes | variable-precisionのいずれかとなります。不正確 | 10進15桁精度 |
| 小型シリアル | 2バイト | 小型自動インクリメント式 integer | 1 to 32767 |
| serial | 4 bytes | autoincrementing integer | 1 to 2147483647 |
| bigserial | 8 bytes |
Monetary Types
money 型には通貨量を固定小数点以下の精度で格納します。 numeric、int、および bigint データ型の値は money にキャストすることができます。 浮動小数点数の使用は丸め誤差が発生する可能性があるため、moneyの処理には推奨されません。
| 名前 | ストレージサイズ | 説明 | 範囲 |
|---|---|---|---|
| money | 8 bytes | currency amount | -92233720368547758.08～+92233720368547758.07 |
Character Types
PostgreSQLで使用できる汎用文字型の一覧を以下に示します。 No.
character varying(n), varchar(n)
可変長（制限付き）
文字（n）, char(n)
固定長, blank padded
text
variable unlimited length
Binary Data Types
bytea データ型では、下表のような2進文字列を保存することができます。
| 名前 | 格納サイズ | 説明 | ||
|---|---|---|---|---|
| bytea | 1 または 4 バイトと実際のバイナリ文字列 | variable- | variable- | bytea |
Date/Time Types
PostgreSQL はSQLの日付と時刻の型の完全なセットをサポートしています。 下表に示すとおりです。 日付はグレゴリオ暦でカウントされます。 ここで、分解能が日である日付型を除いて、すべての型が1マイクロ秒/14桁の分解能を持っています。
| 名前 | 保存サイズ | 説明 | 低値 | 高値 | |
|---|---|---|---|---|---|
| タイムスタンプ | 8 バイト | 日付と時刻の両方（タイムゾーンなし） | 4713 BC | 294276 AD | |
| TIMESTAMPTZ | 8バイト | 日付と時刻の両方です。 タイムゾーンあり | 4713 BC | 294276 AD | |
| date | 4 bytes | date (no time of day) | date (no time of day) | 4713 BC | 5874897 AD |
| time | 8 bytes | time of day (no date) | 00:00:00 | 24:00:00 | |
| time with time zone | 12 bytes | times of day only, with time zone | 00:00:00+1459 | 24:00:00-1459 | |
| 間隔 | 12バイト | 時間間隔 | -178000000 年 | 178000000 年 |
ブール型
PostgreSQL では標準SQLとして Boolean 型を提供しています。 Booleanデータ型はtrue、false、そしてSQLのnull値で表現されるunknownの3つの状態を持つことができます。
| 名前 | ストレージサイズ | 説明 |
|---|---|---|
| ブール値 | 1バイトstate of true or false |
列挙型
列挙型 (enum) は、静的な構成要素からなるデータ型である。 順番に並んだ値の集合です。
他の型とは異なり、列挙型はCREATE TYPEコマンドを使用して作成される必要があります。 この型は、静的で順序付けられた値の集合を保存するために使用されます。 例えば、コンパスの方向、すなわちNORTH、SOUTH、EAST、WESTや、以下に示すような曜日などです。
CREATE TYPE week AS ENUM ('Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun');
列挙型は、一度作成すると、他の型と同様に使用することができます。 最も基本的な型である点は、他のすべての型の基礎を形成する。
| 名前 | ストレージサイズ | 表現 | 説明 | ||
|---|---|---|---|---|---|
| 点 | 16バイト | 平面上の点 | （x,y) | ||
| line | 32 bytes | 無限直線（完全実装ではない） | ((x1,y1),(x2,y2)) | ||
| lseg | 32 bytes | 有限線分 | ((x1,y1),(x2,y2)) | ||
| box | 32 bytes | 角箱((x1.x1.x2.y2)) | ((x1.x1.x2.x2.x2))) | ((x1,y1),(x2,y2)) | |
| path | 16+16n bytes | 閉路（ポリゴンと同様） | ((x1,y1),….） | ||
| path | 16+16n bytes | オープンパス | |||
| polygon | 40+16n | ポリゴン (closed pathと同じ） | ((x1,y1), …),…) | ||
| circle | 24 bytes | Circle | <(x,y),r> （中心点と半径） |
Network Address Type
PostgreSQL では IPv4, IPv6 および MAC アドレスを格納できるデータ型が提供されています。 これらのデータ型は入力エラー検査や特殊な演算子や関数を提供しますので、ネットワークアドレスを格納するためには、プレーンテキスト型の代わりにこれらのデータ型を使用することをお勧めします。
| 名前 | 保存サイズ | 説明 |
|---|---|---|
| cidr | 7 または 19 バイト | IPv4 と IPv6 ネットワーク |
| inet | 7 または 19バイト | IPv4 and IPv6 hosts and networks |
| macaddr | 6 bytes | MAC addresses |
ビット列型
ビット列型 はビットマスクを保存するために使用されます。 それらは0か1のどちらかです。 SQLのビット型には bit(n) と bit varying(n) があり、n は正の整数です。 このタイプには2つのデータ型がある –
| S. No. | Name & Description |
|---|---|
| 1 |
tsvector This is a sorted list of distinct words that have been normalized to merge different variants of the same word, called as “lexemes”. |
| 2 |
tsquery 検索したい語彙を格納し、ブール演算子 & (AND), | (OR), !に従ってそれらを結合している。 (NOT)に従って結合する。 |
UUID Type
UUID (Universally Unique Identifiers) は、小文字の16進数で、ハイフンで分けられたいくつかのグループ、特に8桁からなるグループ、4桁のグループ、12桁からなるグループ、そして128ビットを表す合計32桁で記述されている。
UUIDの例としては、 – 550e8400-e29b-41d4-a716-446655440000
XML Type
XMLデータ型を使用してXMLデータを格納することができる。 XMLデータを格納するためには、まず、次のように関数xmlparseを使用してXML値を作成する必要があります –
XMLPARSE (DOCUMENT '<?xml version="1.0"?><tutorial><title>PostgreSQL Tutorial </title> <topics>...</topics></tutorial>')XMLPARSE (CONTENT 'xyz<foo>bar</foo><bar>foo</bar>')
JSON Type
Jsonデータ型はJSON (JavaScript Object Notation) データを格納するために使用することができます。 このようなデータはテキストとして保存することもできますが、jsonデータ型には、保存された各値が有効なJSON値であることをチェックする利点があります。 また、関連するサポート関数も用意されており、以下のように直接使用してJSONデータ型を扱うことができる。
| 例 | |
|---|---|
| array_to_json(‘{{1,5},{99,100}}’::int) | ,] |
| row_to_json(row(1,’foo’)) | {“f1”:1, “f2”: “foo”} |
配列型
PostgreSQLはテーブルの列を可変長の多次元配列として定義する機会を提供します。 任意の組み込みまたはユーザ定義の基本型、enum型、複合型の配列を作成することができます。
配列の宣言
配列の型は、
CREATE TABLE monthly_savings ( name text, saving_per_quarter integer, scheme text);
またはキーワード “ARRAY” を使用して、
CREATE TABLE monthly_savings ( name text, saving_per_quarter integer ARRAY, scheme text);
値の挿入
配列値は、中括弧内に要素値を囲み、それらをカンマで区切ると、リテラルの定数として挿入することができます。
INSERT INTO monthly_savings VALUES ('Manisha', '{20000, 14600, 23500, 13250}', '{{"FD", "MF"}, {"FD", "Property"}}');
配列へのアクセス
配列にアクセスする例を以下に示す。
SELECT name FROM monhly_savings WHERE saving_per_quarter > saving_per_quarter;
配列の変更
配列の変更の例は次のとおりです。
UPDATE monthly_savings SET saving_per_quarter = '{25000,25000,27000,27000}'WHERE name = 'Manisha';
またはARRAY式構文 –
UPDATE monthly_savings SET saving_per_quarter = ARRAYWHERE name = 'Manisha';
配列の検索
配列の検索の例を以下に示します。
SELECT * FROM monthly_savings WHERE saving_per_quarter = 10000 ORsaving_per_quarter = 10000 ORsaving_per_quarter = 10000 ORsaving_per_quarter = 10000;
配列のサイズがわかっている場合、上記の検索方法を使用することができます。
SELECT * FROM monthly_savings WHERE 10000 = ANY (saving_per_quarter);
Composite Types
この型はフィールド名とそのデータ型、すなわち、フィールドのリストを表します。
複合型の宣言
次の例は、複合型を宣言する方法を示しています
CREATE TYPE inventory_item AS ( name text, supplier_id integer, price numeric);
このデータ型は、以下のようにテーブルの作成で使用できます。
CREATE TABLE on_hand ( item inventory_item, count integer);
複合値入力
複合値は、フィールド値を括弧内に含み、カンマで分離してリテラルの定数として挿入することができます。 以下に例を示す。-
INSERT INTO on_hand VALUES (ROW('fuzzy dice', 42, 1.99), 1000);
これは上で定義されたinventory_itemに対して有効である。
複合型へのアクセス
複合列のフィールドにアクセスするには、テーブル名からフィールドを選択するのと同様に、ドットに続けてフィールド名を使用します。 例えば、on_handテーブルからいくつかのサブフィールドを選択するには、以下のようなクエリになります。 範囲型は離散的な範囲（例えば、1から10のすべての整数値）または連続的な範囲（例えば、, 10:00amと11:00amの間の任意の時点)があります。
内蔵の
-
int4range – integer
-
int8range – bigint
-
numrange – numeric
-
tsrange – – -は以下の範囲のタイプが使用可能である。 タイムゾーンなしのタイムスタンプの範囲
-
tstzrange – タイムゾーン付きのタイムスタンプの範囲
-
daterange – 日付の範囲
カスタム範囲タイプを作成して新しい種類の範囲を利用できるようにすることが可能です。 例えば、inet型をベースにしたIPアドレスの範囲や、floatデータ型をベースにしたfloatの範囲などです。
範囲型は、( )文字を使用して、それぞれ包括的および排他的な範囲境界をサポートします。
オブジェクト識別子型
オブジェクト識別子（OID）は、PostgreSQLが内部的に様々なシステムテーブルの主キーとして使用しているものです。 WITH OIDSが指定されているか、default_with_oids設定変数が有効になっている場合、その場合のみ、ユーザが作成したテーブルにOIDが追加されます。 以下の表はいくつかのエイリアスの種類を示している。 OIDエイリアス型は、特殊な入出力ルーチンを除いて、それ自身は何も操作しない。
| 名前 | 説明 | 値例 | |
|---|---|---|---|
| oid | any | numeric オブジェクト識別子 | 564182 |
| regproc | pg_proc | 関数名 | sum |
| regprocedure | pg_proc | 引数型関数 | sum(int4) |
| regoper | pg_operator | 演算子名 | + |
| regoperator | pg_operator | 演算子の引数型 | *(integer,整数）または-(NONE,整数） |
| regclass | pg_class | 関係名 | pg_type |
| regtype | pg_type | データ型名 | integer |
| regconfig | pg_ts_config | テキスト検索設定 | English |
| regdictionary | pg_ts_dict | テキスト検索辞書 | simple |
疑似型
PostgreSQL型システムには多くの特殊型、つまりは疑似型があります。これらの項目は総称して擬似型と呼ばれています。 擬似型は列のデータ型としては使用できませんが、関数の引数や結果の型を宣言するために使用することができます。 No.
any
関数が任意の入力データ型を受け入れることを示します。
anyelement
関数は任意のデータ型を受け付けることを示します。
anyarray
関数は任意の配列データ型をも受け付けることを示します。
anynonarray
任意の非配列データ型を受け付ける関数があることを示します。
anyrange
任意のレンジデータ型を受け付けることを示します。
cstring
NULL終端のC文字列を受け付けるか返すかを示す関数です。
internal
関数がサーバ内部のデータ型を受け入れるか返すかを示します。
language_handler
手続き型言語呼び出しハンドラはlanguage_handlerを返すと宣言されています。
fdw_handler
外部データラッパーハンドラーはfdw_handlerを返すように宣言されています。
record
不特定の行型を返す関数を識別します。
trigger
トリガー関数は、トリガーを返すよう定義されています。
void
関数が値を返さないことを示す。