このサイトは、もともと作者の自分用メモとして書き始めたものです。書いてあることが全て正しいとは限りません。他の文献、オフィシャルなサイトも確認して、自己責任にて利用してください。

運用のTips [クエリ]

説明のために使用するデータベース "sample" (スーパーの商品管理風？) ：

手っ取り早く実験してみたい人のために、sample データベースのダンプを置いておく。ダンプ時に -C は指定してないので、自由な名称でデータベース (入れ物) を作ってからリストアすべし。文字コードは EUC になっている。

副問い合わせ (Sub Query) 評価式

サブクエリからの結果を利用してクエリを行うことができる。複数のテーブルの値を参照する複雑なクエリを、ひとつのクエリで済ませることができる。phpなどで利用するとき、変数を増やさずに済む。副問い合わせ部分は ( ) で囲まなければならない。副問い合わせ評価演算子にはこの他、EXISTS, ALL, SOME, ANY がある。

= を使う

SELECT kcord, kamoku FROM tkamoku
 WHERE bcord = ( SELECT bcord FROM tbunrui WHERE bunrui = 'カシ' );

結果：

"=" の場合、サブクエリは必ず１行１カラムを返さなければならない。反対の意味は "<>"。

IN を使う

SELECT kcord, kamoku FROM tkamoku WHERE bcord
 IN (
 SELECT bcord FROM tbunrui WHERE bunrui = 'カコウショクヒン' OR bunrui = 'カシ');

結果：

"IN" の場合、サブクエリが返す値 (行) は複数でもよい。複数カラムはダメ。これは、IN が配列を受け付け可能で、こういう使い方もできるから。

SELECT kcord, kamoku FROM tkamoku WHERE kcord IN (1, 2);

「kcord の値が 1 または 2」のレコードを取り出すので結果は同じ。意味の反転は "NOT IN"。
詳細は、PostgreSQL 7.3.x ユーザガイド

なお、上記クエリは、パターンマッチングの POSIX 正規表現を使って下のように書いても同じ結果が得られ、場合によっては、正規表現マッチの方が検索コストが低くくできる；

SELECT kcord, kamoku FROM tkamoku WHERE kcord ~ '^[12]$';

さらに、同じ検索は WHERE ... OR を使って；

SELECT kcord, kamoku FROM tkamoku WHERE kcord = 1 OR kcord = 2; 

とも書けるわけだが、筆者の経験からすると、たいていの場合 POSIX 正規表現と IN のどちらよりも検索コストが悪化する。

パターンマッチング

WHERE 句で、「まさにこの文字列」ではなく、近似表現でレコードを取り出したいことがよくある。 PostgreSQL には、2種類のパターンマッチング演算子があったが、7.3 で "SIMILAR TO" が増えて3種類になった。
※詳細は、PostgreSQL 7.3.x ユーザガイド

POSIX正規表現 "～"

Perl や egrep で馴染み深い正規表現が使え、最も柔軟性が高い。Perl 正規表現の "/i" のように大文字小文字を区別したくない場合は "~*" を、全体を否定するには "!~" が使える。 7.4 でさらに拡張が進んでほぼ Perl 正規表現近似となり、前方先読参照や、結果に格納しないグループ参照、\b, \n, \r, \t などのエスケープ表現も使えるようになった (pgsql.conf 参照)。ただし、高機能な分、作業コストがやや高く、複雑なマッチが必要ない場面では後述の LIKE のほうが検索時間が短くできることが多い。

例：

SELECT scord, hinmei FROM tshohin WHERE hinmei ~ '^カ.*ポ[タチツテ]ト(シオ|ミソ)$';

結果：

注意点： POSIX 正規表現は部分マッチであり、 ^ と $ を使わないと、思惑よりもたくさんの文字列がマッチしてしまう。例えば上記の例で頭の ^ を付けずに；

SELECT scord, hinmei FROM tshohin WHERE hinmei ~ 'カ.*ポ[タチツテ]ト(シオ|ミソ)$';

とすると、「ダカルヒーポテトシオ」などにもマッチしてしまうので注意が必要だ。また、「カルヒーポテト」の後に続く文字が何でもいい場合には；

SELECT scord, hinmei FROM tshohin WHERE hinmei ~ '^カ.*ポ[タチツテ]ト.*';

と書く必要性はなく、

SELECT scord, hinmei FROM tshohin WHERE hinmei ~ '^カ.*ポ[タチツテ]ト';

でも同じ結果が得られる。

LIKE

伝統的な SQL互換のパターンマッチング。mysql にも oracle にもある。移植性の高いコードを書きたければ、これ (？)。大文字小文字を区別しないマッチングには ILIKE があるが、こちらは PostgreSQL 固有。

LIKEの特殊文字：

例： (結果は上記と同じ)

SELECT scord, hinmei FROM tshohin WHERE hinmei LIKE '_ルヒーポテト%';

否定するには、NOT LIKE

SIMILAR TO

PostgreSQL 7.3 で新たに導入された、SQL99 準拠の演算子。LIKEの拡張版で、POSIX 的な以下の特殊文字も解釈される。

SIMILAR TO の追加特殊文字：

例： (結果は同じ)

SELECT scord, hinmei FROM tshohin WHERE hinmei SIMILAR TO 'カ[リル]_+(シオ|ミソ)';

結合テーブル

JOIN は、２つのテーブルを結合した答を得る。

例：

SELECT * FROM tshohin s LEFT JOIN tkamoku k ON (s.kcord = k.kcord) WHERE bcord = 2;

結果：

上のやり方では、tshohin の kcord カラムと tkamoku の kcord カラムが重複出力されてしまう。これを避けるには：

SELECT * FROM tshohin s LEFT JOIN tkamoku k USING (kcord) WHERE bcord = 2;

とするか、

SELECT * FROM tshohin s NATURAL LEFT JOIN tkamoku k WHERE bcord = 2;

USING で両方に共通するカラムを指定する。複数あるなら (culumn1, culumn2 ) とする。NATURAL はこれを自動的にやってくれる。

現実には、上記のように2つのテーブルのすべてのカラムを取り出すことは少ないだろう。必要なカラムだけ取り出すよう明示するのが一般的。

より現実的な例：

SELECT k.kamoku, s.hinmei, s.joudai FROM tshohin s LEFT JOIN tkamoku k
 USING (kcord) WHERE k.bcord = 2;

結果：

LEFT の他に、RIGHT, CROSS, FULL, INNER の JOIN があるが、使う必要に迫られた経験無し。なお、LEFT, RIGHT は各々 "LEFT OUTER", "RIGHT OUTER" の略。ともに必ず OUTER なので記述する必要は無い。
※詳細は、PostgreSQL 7.3.x ユーザガイド または チュートリアル

3つのテーブルを結合するにはJOINを2回行う：

SELECT v.bunrui, v.kamoku, s.hinmei, s.joudai FROM tshohin s LEFT JOIN (
 SELECT * FROM tkamoku LEFT JOIN tbunrui USING (bcord)
) v
 USING (kcord) WHERE bcord = 2;

こう書くこともできる：

SELECT v.bunrui, v.kamoku, s.hinmei, s.joudai FROM tshohin s LEFT JOIN (
 tkamoku LEFT JOIN tbunrui USING (bcord)
) v
 USING (kcord) WHERE bcord = 2;

結果：

※多重JOINの例はPostgreSQL 7.3.x ユーザガイド 「明示的なJOINでプランナを制御」

テーブルのエイリアス

上の例の "s" や "k" や "v" のこと。2つのテーブルを使用した問い合わせでは、例えば kcord カラムは両方のテーブルにあるため、どちらのテーブルのカラムか明示してやらないと「カラム名が曖昧だ」 (ambiguous) と言われてエラーとなる。その時便利なのが、テーブルのエイリアス名。エイリアスを使用しないと、

SELECT tkamoku.kamoku, tshohin.hinmei, tshohin.joudai FROM tshohin
 LEFT JOIN tkamoku ON (tkamoku.kcord = tshohin.kcord) WHERE tkamoku.bcord = 2;

のようになってしまう。例ではすべてのカラムでテーブルを明示したが、片方のテーブルにしかないカラムについては明示する必要は無い。

集約(aggregate)関数

avg (平均), count (数値、個数), max (最大値), min (最小値), sum (合計), stddev (標準偏差標本), variance (分散標本) がある。

count

sampleテーブル：

SELECT count(*) FROM sample;   --> 結果：2　(sample テーブルの総行数を数えた)
SELECT count(val) FROM sample; --> 結果：1　(val カラムを基準に総行数を数えた。値が NULL だとカウントされない)

※PostgreSQL 7.3.x ユーザガイド または チュートリアル

グループ化(GROUPとHAVING)

HAVING は、WHERE に似ているが、グループ化した行を、条件に従ってグループ単位でごっそり選択/除外してくれる。

SELECT kcord, avg(joudai) FROM tshohin GROUP BY kcord HAVING kcord IN (1,3,5);

結果：

上代が600円以上のものは計算に入れたくない場合：

SELECT kcord, avg(joudai) FROM tshohin WHERE joudai < 600
 GROUP BY kcord HAVING kcord IN (1,3,5);

結果：

※PostgreSQL 7.3.x ユーザガイド