| [ホーム] -> [Aache + PHP + PostgreSQL 実験室] |
PostgreSQL では、いろいろなデータ型が用意されています。ほぼ SQL92 に準拠していますが、当然特有のデータ型も装備されています。歴史的な経緯もあり、今では他の方法にとって変わられたデータ型もあります。まず、基本的なものから説明しましょう。主に、SQL92 互換のデータ型です。
| データ型 | 説明 |
|---|---|
| int2 | 符合付正数（smallint） |
| int4 | 符合付正数（numeric） |
| float8 | 倍精度浮動少数（real） |
| char(n) | 固定長文字列 |
| varchar(n) | 可変長文字列 |
| date | 日付（年月日） |
| time | 時刻（時分秒） |
| datetime | 日付と時刻 |
主に使われるデータ型はこんなところです。当然のことですが、これ以外にもたくさんあります。char と varchar は引数として桁数を指定します。char の場合、固定長の文字列となります。たとえば、char(10) として１０文字の文字を格納できるカラムを定義した場合、７文字しか格納しないと残りの部分にはスペースが挿入されます。それに対し、varchar は文字が少なくても後ろに名にも付けられません。もっとも、PostgreSQL では、文字列型は 次に説明する textを使った方が効率が良いとされています。日付と時刻に関しては、abstime や timestampなどありますが、古くに実装した機能で、今となっては使い勝手が悪いので、datetime 使うのが良いようです。
次に、PostgreSQL 特有のデータ型の説明です。
| データ型 | 説明 |
|---|---|
| text | 最大文字数を指定する必要のない可変長文字列 |
| box | 1.0, 2.0 のように矩形を指定できる |
| circle | 中心(x,y)、半径 r の円を x,y,r で表せる |
| lseg |
x1,y1 から x2,y2 までの直線を (x1,y1),(x2,y2)といった形式で表せる
|
| path |
経路上の点のリストを (x1,y1),(x2,y2),... といった形式で表現できる
|
| point | １点を x,y といった形式で表現できる |
| polygon |
(x1,y1),(x2,y2),... といった形式で点を結んで出来る多角形を表現できる
|
text は最大文字数を指定する必要がありません。また、char、varchar に比べて効率が良いので、文字列を扱う場合は text の使用が勧められています。
この様に、PostgreSQL では、座標系のデータ型を装備しています。地理データや、図形データに対して非常に親和性が高いのではないでしょうか。まあ、私自身は、これらのデータ型を使ったことがないので、どう言ったときに有用かは、ちょっとわからないのですが、必要としている分野もたくさんあることでしょう（だからこそ実装されているのでしょうし）。
演算子に関しても、いろいろな演算をサポートしています。他の RDBMS だと、関数としてサポートしているものも、演算記号としてサポートしていたりします。
| 演算子 | 説明 |
|---|---|
| + - * / | 四則演算子です |
| % | 乗余り（モジュラ）です |
| @ | 絶対値を求めます（@-5.0 = 5.0） |
| ^ | ベキ乗を求めます（2^5 = 32） |
| ; | log を求めます（(;5.0) = log5） |
| |/ | ルート演算します（|/25.0 = 5） |
| ||/ | 3 ルートを求めます（||/27 = 3） |
| || | 文字列を連結します |
以上のように、多数の演算子が用意されています。実際に使ってみて試してください。
=> select |/25;
この様にすると、計算結果が表示されます。一部の RDBMS と違い、from でテーブル名を指定する必要はありません。
いろいろな組み込み関数がありますが、簡単なものだけ紹介させていただきます。
| 関数 | 説明 |
|---|---|
| initcap('string') |
'string' 内の単語の先とを大文字にした文字列を返します。
|
| position('string' in 'source') |
'source' の中から 'string' を探し、開始位置を返します。開始位置は 1 から始まります。見つからない場合は 0 を返します。
|
|
substr('string', from_position[, for_position]) |
'string' の from_position から for_position までを取り出します。'for_position' が省略された場合は最後までを意味します。
|
|
trim([leading | trailing | both] ['omitt_text'] from 'string') |
'string' の leading（先頭）あるいは trailing（末尾）あるいはboth（両端）から 'omitt_text' を取り除いた文字列を返します。
|
| upper('string') |
'string' を大文字に変換した文字列を返します。
|
| lower('string') | 'string' を小文字に変換した文字列を返します。 |
| charcter_length('string') |
'string' の文字数を返します。日本語も１文字につき１と数えます。
|
| octet_length('string') |
'string' のバイト数を返します。
|
主に文字列操作の関数が多いですが、もちろんこれ以外にもたくさんの関数が用意されています。また、これらも select 文を使うことにより、どのような結果が返るか試せるので、テストしてみてください。
PastgreSQL では、他の RDBMS に負けない（あるいはそれ以上の）複雑な問い合わせが実行できます。まず、簡単な like について説明しましょう。
=> select * from table where name like 'a%'
この様な select 文を発行すると、name カラムの値が a で始まるものが選択されます。つまり、パーセント「%」がワイルドカード（０文字以上の任意の文字）に当たります。もし、１文字だけに限定したい場合は、アンダーバー「_」を指定します。似たようなもので、PostgreSQL では、正規表現検索が行えます。チルダ「~」を使います。
=> select * from table where name ~ '^a'
これは、grep などのコマンドで使える正規表現で検索を行います。上記の結果は、先ほどと同じ a で始まるのものが選択されます。正規表現の文字として、「^$[].+*」などがサポートされているようです。
副問い合わせとは、簡単に言えば、select の結果を where 句などに指定できる、と言うことです。次の一連の問い合わせを見てください。
=> select 1;
?column?
--------
1
(1 row)
=> select * from test where code = 1;
code|name
----+-----
1|sdap
(1 row)
=> select * from test where code = (select 1);
code|name
----+-----
1|sdap
(1 row)
まあ、これに何の意味があるのかと言われるとちょっと困るのですが、これが副問い合わせの簡単な例です。select 1; は必ず 1 を返すので、その結果を code = と一致するものと検索をしたのです。今回は、select 1 と固定の値を返しましたが、実際はテーブルから検索した結果などと照らし合わせて、問い合わせを行うことができるのです。この機能があるのと無いのとでは、結構プログラミングをするときの手間が違ってきます。実際、大抵は副問い合わせを行わなくても、複数回 select 文を発行すれば済む場合があるのですが、パフォーマンスとプログラミングの手間を考えた場合、副問い合わせで済ましてしまう方が便利な場合が多いです。