この章では、配布された状態でユーザが使用できる Postgres のデータ型、関数および演算子について説明している。
組み込み型はすべてのデータベースにインストールされる。psqlでは \d でこれらの型を表示することができる。
ユーザは、このマニュアルで説明されているdefine typeコマンドで新しい型を追加することもできる。
SQL/92 文法において定義されているデータ型の中には、直接ネイティブの Postgres 型にマッピングされているものがある。正確な数値 であるdecimalとnumericは完全に実装されているが、現在 (postgres v6.2) は SQL/92 で認められている限定された値の範囲しかサポートしていないことに注意すること。
| POSTGRES Type | SQL/92 Type | Meaning |
|---|---|---|
| char(n) | character(n) | fixed-length character string |
| varchar(n) | character | varying(n) variable-length character string |
| float4/8 | float(p) | floating-point number with precision p |
| float8 | double | precision double-precision floating-point number |
| float8 | real | double-precision floating-point number |
| int2 | smallint | signed two-byte integer |
| int4 | int | signed 4-byte integer |
| int4 | integer | signed 4-byte integer |
| int4 | decimal(p,s) | exact numeric for p <= 9, s = 0 |
| int4 | numeric(p,s) | exact numeric for p == 9, s = 0 |
| timestamp | timestamp | with time zone date/time |
| timespan | interval | general-use time span
|
SQL／92 で定義されている定数と関数がある。
| SQL/92 Function | Meaning |
|---|---|
| current_date | date of current transaction |
| current_time | time of current transaction |
| current_timestamp | date and time of current transaction
|
組込み型の多くは明確な外部フォーマットを持つ。しかしながら、開いた／閉じたパスのように Postgres 固有であったり、日付や時刻型のようにいくつかのフォーマットを持ち得る型もある。
日付と時刻型のほとんどは、データ入力においてコードを共有している。これらの型(datetime ,abstime ,timestamp ,timespan ,reltime ,date ,time )においては、いろいろなスタイルを持った入力パターンが考えられる。数値型表現においてはヨーロッパと US の習慣は異なっているので、データを入力する前にset(l) コマンドを使って適切な解釈方法を与えてやることになる。出力フォーマットは、ISO-8601, SQL (伝統的 Oracle/Ingres)、および、ヨーロッパ型と US 型のバリエーション(set(l) を参照)を持つ伝統的 Postgres(absolute timeの章を参照)のうちのいずれかのスタイルとなる。
将来のリリースでは日付／時刻型の数が減っていき、現在の実装における datetime は timestamp に、timespan は interval に変更され、そしておそらく timestamp と interval が好んで使われるので、abstime と reltime はなくなってゆくであろう。
日付と時刻の一般的な使い方としては、ISO 互換、SQL 互換、伝統的Postgres(absolute timeの章を参照)を含むさまざまなスタイルでの入力や、その他の日付と時刻を表すものの代替としてである。出力スタイルは、ISO 互換、SQL 互換、または Postgres v6.0 との互換性のためにセットされる、デフォルトの伝統的 Postgres のいずれかである。
datetime は以下の書式により指定される。
年-月-日 [ 時 : 分 : 秒 ] [AD,BC] [ タイムゾーン ]
年月日 [ 時 : 分 : 秒 ] [AD,BC] [ タイムゾーン ]
月 日 [ 時 : 分 : 秒 ] 年 [AD,BC] [ タイムゾーン ]
ここで、
年は 4013 BC 〜 巨大な数
月は Jan, Feb 〜 Dec または 1, 2 〜 12
日は 1, 2 〜 31
時は 00, 01 〜 23
分は 00, 01 〜 59
秒は 00, 01 〜 59 (60 はうるう秒)
タイムゾーンは 3 文字または GMT に対する ISO オフセット
である。
有効な日付は Nov 13 00:00:00 4013 BC GMT から遠い未来までである。タイムゾーンは 3 文字(たとえば GMT や PST)またはGMT に対する ISO 互換オフセット(たとえば太平洋標準時においては -08 や -08:00)である。日付は内部的にはグリニッジ標準時で格納される。入出力ルーチンでは、時刻をサーバのローカル・タイムゾーンに変換する。
特別な値 `current'、`infinity' および `-infinity' が提供されている。`infinity' はすべての有効な時間より後を示し、`-infinity' はすべての有効な時間より前を示す。`current' は、計算時にこの値が現れる時は常に現在時刻に置き換えられることを示す。
`now', `today', `yesterday', `tomorrow' および `epoch' という文字列は、時間の値を表すのに使用できる。`now' は現在時刻を意味しており、即時に現在時刻に置き換えられる`current' とは異なる。`epoch' は Jan 1 00:00:00 1970 GMTを表す。
期間の一般的な使い方としては、ISO 互換、SQL 互換、伝統的Postgres(absolute timeの章を参照)を含むさまざまな書式での入力や、その他の期間を表すものの代替としてである。出力フォーマットは、ISO 互換、SQL 互換、またはPostgres との互換性のためにセットされる、デフォルトの伝統的 Postgres のいずれかである。月と年は 定性 時間間隔であり、日や時間などその他の定性 時間間隔とは区別して格納される。日数計算をする場合には、定性時間単位は関連する日付や時間の内容において具体化される。
期間は以下の書式により指定される。
量 単位 [時間単位...][方向]
@ 量 単位 [方向]
ここで、
量は ..., `-1', `0', `1', `2', ...
単位は `second', `minute', `hour', `day', `week', `month', `year'
またはこれらの単位の省略形や複数形、方向は `ago' です。
絶対時間 (abstime) は限られた範囲(前後 68 年間)と限られた精度(1 秒)の日付データ型である。datetimeの方が、より大きな範囲と精度をカバーするので良く使われる。
絶対時刻は以下の書式により指定されます。
月 日 [ 時 : 分 : 秒 ] 年 [ タイムゾーン ]
ここで、
月は Jan, Feb, ..., Dec or 1, 2, ..., 12
日は 1, 2, ..., 31
時は 00, 02, ..., 23
分は 00, 01, ..., 59
秒は 00, 01, ..., 59 (60 はうるう秒)
年は 1901, 1902, ..., 2038
有効な日付は Dec 13 20:45:53 1901 GMT から Jan 19 03:14:04 2038 GMT までです。Version 3.0 では、もはや時刻はグリニッジ標準時では読み書きされません。入出力ルーチンのデフォルトはローカルのタイムゾーンを参照するようになっています。
datetimeに対するすべての特殊な値は、そのまま 絶対時刻に対しても使用できます。
相対時刻 (reltime) は、限られた範囲(前後 68 年間)と限られた精度(1 秒)をもつ期間指定データ型です。timespanの方がより大きな範囲と精度を持ち、1 つのエントリで複数の単位を使用することができ、また年や月といった定量的な時間を正確に処理できるため好まれます。一方 reltime は、1 つのエントリにつき 1 つの量と 1 つの単位しか使えないので複雑な期間を扱うには不便です。
相対時刻は以下の書式で指定されます。
@ 量 単位 [方向]
ここで、
量は ..., `-1', `0', `1', `2', ...
単位は `second', `minute', `hour', `day', `week', `month', `year'
またはこれらの単位の省略形や複数形、方向は `ago' です。
.RB (注意:有効な相対時刻は 68 年以下です)また、特殊な相対時刻である"未定義の RelTime" が提供されています。
これは現在では、abstime データ型に非常に良く似た、限られた範囲を持つ絶対時刻です。これは、汎用入力パーザを他の日付／時刻型と共有しています。将来のリリースでは、この型は datetime 型の機能を吸収し、今後の SQL92 準拠に移されます。
timestamp は datetime と同じ書式を使って表されます。
時間の範囲は以下の書式で表されます。
[ 'abstime' 'abstime']
ここで、abstimeは絶対時刻フォーマットの時刻です。特別な abstime 値である"current", "infinity" および"-infinity" も使えます。
座標点は以下の書式で指定されます。
( x , y )
x , y
ここで
x は X 座標値（浮動小数点）
y は Y 座標値（浮動小数点）
lseg is specified using the following syntax:線分 (lseg) は以下の書式で指定されます。
( ( x1 , y1 ) , ( x2 , y2 ) )
( x1 , y1 ) , ( x2 , y2 )
x1 , y1 , x2 , y2
ここで
(x1,y1) および (x2,y2) は線分の終端
長方形(boxes)は、その対角となる座標点の組で表されます。
長方形は以下の書式で指定されます。
( ( x1 , y1 ) , ( x2 , y2 ) )
( x1 , y1 ) , ( x2 , y2 )
x1 , y1 , x2 , y2
ここで
(x1,y1) および (x2,y2) は対角線上の角
長方形は，最初の書式を使って出力されます。2 つの角は、左下の角を最初に，右上の角を最後に格納するように並べ替えられます．長方形の中の他の角を入力することもできますが，左下と右上の角が入力により決定され，格納されます．
パス(path)は座標点のセットにより表現されます．パスは，座標点セットの中の最初の点と最後の点が接続されていない「開いた」状態か，接続されている「閉じた」状態かのいずれかです．あるパスを強制的に開いた／閉じた状態にする.OR popen(p)とpclose(p)が提供されており，またisopen(p)とisclosed(p)を使って、問い合わせの中でいずれかのタイプを選択することができます．
パスは以下のような書式で指定します．
( ( x1 , y1 ) , ... , ( xn , yn ) )
[ ( x1 , y1 ) , ... , ( xn , yn ) ]
( x1 , y1 ) , ... , ( xn , yn )
( x1 , y1 , ... , xn , yn )
x1 , y1 , ... , xn , yn
ここで，
(x1,y1),...,(xn,yn) は 1 から n までの座標点
[ で始まるのは開いたパス
( で始まるのは閉じたパス
を表します。
パスは最初の書式を使って出力されます．Postgres の v6.1 より前のバージョンまでは，パスの書式として，( で始まる「閉じた」フラグ，座標点の数を表す 1 つの整数カウンタ，座標点のリスト，最後に閉じ括弧 ) という形式を使用していました．v6.1 以前のデータベースからダンプおよびリロードされたパスを変換するために，組み込み関数 upgradepath() が提供されています。
多角形(poligon)は座標点のセットで表現されます．多角形は閉じたパスと同じであると考えられますが，異なった方法で格納され，また双方固有のサポートルーチンのセットを持っています．
多角形は以下の書式で指定されます．
( ( x1 , y1 ) , ... , ( xn , yn ) )
( x1 , y1 ) , ... , ( xn , yn )
( x1 , y1 , ... , xn , yn )
x1 , y1 , ... , xn , yn
ここで，
(x1,y1),...,(xn,yn) は 1 から n までの座標点
多角形は最初の書式により出力されます．最後のフォーマットはv6.0 以前のパスのフォーマットとして過去への互換性のために残されていますが、将来の postgres のバージョンではサポートされなくなるでしょう。先行する単独の ( は v6.0 互換フォーマット( x1 , ... , xn , y1 , ... , yn )であることを表します。Postgres の v6.1 より前のバージョンまでは，多角形の書式として，( ，X 座標のリスト，Y 座標のリスト，最後に閉じ括弧 ) という形式を使用していました．v6.1 以前のデータベースからダンプまたはリロードされた多角形を変換するために，組み込み関数upgradepoly() が提供されています．
円(circle)は中心点と半径により表現されます．
円は以下の書式により指定されます．
< ( x , y ) , r >
( ( x , y ) , r )
( x , y ) , r
x , y , r
ここで，
(x,y) は円の中心点
r は円の半径 を表します。
円は最初の書式で出力されます．
Postgresでは，システム型に対して多くの種類の組み込み演算子が提供されています．これらの演算子は，システムカタログ "pg_operator" の中で宣言されています．"pg_operator" の中の各エントリは，演算子を実装する手続きのオブジェクト ID により構成されています．
ユーザは、演算子の名前を使って以下のよう演算子を呼び出します。
select * from 従業員 where 給与 < 40000;
また、ユーザは演算子直接実装した関数を呼ぶこともできます。この場合は、上の問い合わせは以下のように表現されます。
select * from 従業員 where int4lt(給与, 40000);
psqlではこれらの演算子を表示する \d コマンドが提供されています。
多くのデータ型においては、関連する他の型への変換をするための関数が用意されています。さらに、その型特有の関数を持つものもあります。演算子を通しても使用できるような関数は、演算子としてのみ文書化されています。
text型として定義されている関数の中には、char() や varchar() においても使用できるものがあります。
date_part() 関数および date_trunc() 関数においては、引数として、`year'、`month'、`day'、`hour'、`minute'、`second' に加え、より限定した量を表す `decade'、`century'、`millenium'、`millisecond'、`microsecond' も使えます。date_part() 関数では、曜日を返すための `dow' や、datetime のために 1970 年からの通算秒を返す `epoch' や、timespanのために通算秒の合計を返す'epoch' も使えます。
関数： 整数 float8 float(int) 整数を浮動小数点に変換 float4 float4(int) 整数を浮動小数点に変換 浮動小数点 int integer(float) 浮動小数点を整数に変換
テキスト text lower(text) テキストを小文字に変換 text lpad(text,int,text) 指定された長さまで文字列を左から詰める ltrim(text,text) テキストの左から文字を切り取る text position(text,text) 指定された文字列の抽出 rpad(text,int,text) 指定された長さまで文字列を左から詰める rtrim(text,text) テキストの右から文字を切り取る text substr(text,int[,int]) 指定された文字列の抽出 text upper(text) テキストを大文字に変換
絶対時間 bool isfinite(abstime) 有限の時間であれば真 datetime datetime(abstime) datetime へ変換
日付 datetime datetime(date) datetime へ変換 datetime datetime(date,time) datetime へ変換
日付と時刻 timespan age(datetime,datetime) 月と年を保持して date 差分を取る float8 date_part(text,datetime) 日付フィールドの指定された部分 datetime date_trunc(text,datetime) 指定された単位での日付の切り捨て bool isfinite(datetime) 有限の時間であれば真 abstime abstime(datetime) abstime へ変換
相対時刻 timespan timespan(reltime) timespan へ変換
時間 datetime datetime(date,time) datetime へ変換
期間 float8 date_part(text,timespan) 時刻フィールドの指定された部分 bool isfinite(timespan) 有限の時間であれば真
長方形 box box(point,point) 座標点を長方形に変換 float8 area(box) 長方形の面積
パス bool isopen(path) 開いたパスであれば真 bool isclosed(path) 閉じたパスであれば真
円 circle circle(point,float8) 円に変換 polygon polygon(npts,circle) npts 個の頂点を持つ正多角形に変換 float8 center(circle) 円の中心点 float8 radius(circle) 円の半径 float8 diameter(circle) 円の直径 float8 area(circle) 円の面積
SQL/92 では関数を特定の書式で定義している。これらのうちのいくつかでは、その他の Postgres 関数を使って実装されている。
SQL/92 関数
テキスト
text position(text in text) 指定された文字列を抽出
text substring(text [from int] [for int])
指定された文字列を抽出
text trim([leading|trailing|both] [text] from text)
テキストから文字列を取り除く
psqlでは、システム情報を表示するためのさまざまな \d コマンドが提供されています。詳細な一覧表は、これらのpsqlコマンドを使用してください。
set(l) ,show(l) ,reset(l) ,psql(1) .組込み型に対する定数を指定するための例はSQL (l)を参照してください。
(整数や浮動小数点といった)ベース型に対応するほとんどの入出力関数はエラーチェックを行いますが、特別厳格なチェックを行わないものもあります。特に、(加算や乗算のように)まったくエラーチェックを行わない演算子や関数もあります。したがって、多くの(たとえば)数値演算子はアンダーフローやオーバーフローが起こったことに気が付きません。
入出力関数のうちのいくつかは可換ではありません。すなわち、ある出力の結果をその計算前の入力と比べた時、精度が失われる場合もあります。
堀田 倫英 <hotta@net-newbie.com>