Edited by
和訳
by The PostgreSQL Development Team
Edited by Thomas Lockhart
(last updated 1999-05-19)
Translated by Japan PostgreSQL User Group (1999-11-06)
| クラス |
| 継承 |
| 型 |
| 関数 |
他にもの追加パワーと柔軟性を提供する特徴があります:
| 制約 |
| トリガ |
| ルール |
| 完全なトランザクション |
このような特徴が Postgresを オブジェクトリレーショナル とされるデータベースのカテゴリに置いています。
通常、伝統的なリレーショナルデータベース言語をサポートすることに向いていない オブジェクト指向
とされるものとはまったく別物であることに注意してください。 ですから、Postgres
は いくらかのオブジェクト指向の特徴を持ってはいますが、 リレーショナルデータベースの世界にしっかりと存在しています。
実際、いくつかの商用データベースは最近 Postgres が率先してきた特徴を組み込み始めました。
それから Postgres はいくつかのメジャーリリースを経験してきました。 最初の "デモウェア" システムが 1987 年に使えるようになり、 1888 年の ACM-SIGMOD 会議でお目見えしました。 The Implementation of Postgres に説明されるバージョン 1 を、 1989 年 1 月に数人の外部ユーザへリリースしました。 最初のルールシステム (A Commentary on the Postgres Rules System) の批評に応えて、 ルールシステムは再設計され (On Rules, Procedures, Caching and Views in Database Systems)、 新しいルールシステムとともにバージョン 2 が 1990 年 6 月にリリースされました。 複数保存領域管理、問い合わせ実行モジュールの改善、 書き直した書き換えルールシステムのサポートが追加された バージョン 3 が 1991 年に出されました。 それから Postgres95 (下記参照) までのリリースは、 大方、移植性と信頼性の向上に焦点が合わされていました。
Postgres は色々な研究や製造のアプリケーションを実装するのに使われてきました。 これらには 財務データ分析システム、 ジェットエンジンのパフォーマンスモニター・パッケージ、 惑星軌道追跡データベース、 その他、地理情報システムなどが含まれています。 Postgresはまた いくつかの大学で教育ツールとしても使われてきました。 最終的には、Illustra Information Technologies (その後 Informix に合併されました) がコードを取り出し、商品化しました。 Postgres は 1992 年後半の科学計算プロジェクト Sequoia 2000 の基本データマネージャとなりました。
1993 年中に外部ユーザコミュニティのサイズは倍近くになりました。 プロトタイプのコードのメンテナンスとサポートは、 データベースの研究に当てるべき時間の多くを取っていくことは ますます明白となりました。 このサポート負担を軽減しようとする中で、 プロジェクトは公的には バージョン 4.2 で終りを告げました。
Postgres95 コードは完全に ANSI C であり、 そのサイズは 25% 切り落とされました。 多くの内部的な変更で、パフォーマンスとメンテナンス性が向上しました。 Postgres95 v1.0.x は Postgres v4.2 と比べて Winsconsin ベンチマークで 約 30-50% 速く動きました。 バグフィクスは別として、次のような主な機能拡張があります。
monitor プログラムに加えて、 GNU の readline を使い インタラクティブに SQL 問い合わせを行なう 新しいプログラム (psql) が提供されました。
新しいフロントエンドライブラリ libpgtcl が Tclベースのクライアントをサポートしました。 サンプルシェルの pgtclsh が tcl と Postgres95 バックエンドとを結ぶ 新しい Tcl コマンドと提供しました。
巨大オブジェクトのインターフェイスがオーバーホールされました。 転置巨大オブジェクト(Inversion large object)は巨大オブジェクトを格納する 唯一のメカニズムでした。（転置(Inversion)ファイルシステムは削除されました。）
インスタンスレベルでのルールシステムが削除されました。 今は、書き換えルールとして利用できます。
正規の SQL の特徴を紹介する短いチュートリアルが、 Postgres95 のものと共にソースコードと一緒に配布されました。
GNU make が(BSD makeの代わりに) ビルドに使われました。 また、Postgres95 はパッチを当てていない gcc で コンパイルできました(double のデータ並びが修正されました)。
Postgres95の開発での重点は バックエンドのコード中にある既存の問題点を見極めることにありました。 PostgreSQL の開発作業は、すべての領域で続けてますが、 重点は機能や性能を向上させることにシフトしました。
主な PostgreSQL での機能拡張には以下のものがあります。
副問い合わせ、デフォルト値、制約、トリガといった重要なバックエンドの特徴が 実装されました。
プライマリキー、クオートされた記述子、強制型指定、型キャスト、 バイナリと16進整数入力を含む、追加的な SQL92 準拠の 言語の特徴が加えられました。
組み込み型が改善されて、新たに日付/時間型の幅広い指定、追加的な幾何学型のサポート が含まれます。
v6.0 がリリースされたときから比べて 、バックエンドコード全体のスピードがおおよそ 20-40% 向上し、 バックエンドの起動時間は 80% 減少しました。
ここからは、 Postgres が PostgreSQL として配布されているバージョンのことを意味することにします。
現在サポートされているマシンのリストについては管理者ガイドをチェックして下さい。
一般的には、完全な libc ライブラリをサポートしている Unix/Posix
互換であれば、どんなシステムでも Postgres を移植できます。
充分な経験がないような人でもドキュメントの修正や小さな変更は提供できます。 それが始めるにあたって良い方法でしょう。 pgsql-docs (アーカイブ) メーリングリストで行なわれています。
Postgres の スーパユーザ は postgres という名前のユーザで、 Postgres バイナリとデータベースのファイル を所有する者です。 データベースのスーパユーザとして、 すべての保護メカニズムは無視され、すべてのデータに任意にアクセスできます。 加えて、Postgres のスーパユーザは 通常すべてのユーザは利用できないいくつかのサポートプログラムを 実行することが許されています。 Postgres のスーパユーザは、 (rootとして参照できる)Unix のスーパユーザと 同じではないことに注意してください。 スーパユーザはゼロ以外のユーザID (UID) を持っています。
データベース管理者 もしくは DBA はサイトのセキュリティ方針を強要するメカニズムで Postgres をインストールする責任を持つ人です。 DBA は下記の方法で新しいユーザを加えることができ、 createdb で使われる テンプレートデータベースのセットを維持します。
postmaster は Postgres システムへのリクエストのための 交換所の役割を果たすプロセスです。 フロントエンドアプリケーションは システムエラーやバックエンドプロセスとの接続を追跡する postmaster に接続します。 postmaster はその動作を調整するために、 いくつかのコマンドライン引数を取ることができます。 しかしながら、引数を与えることは、 複数のサイトやデフォルトとは違うサイトを走らせようとする時 のみに必要となるものです。
Postgres バックエンド (実際の実行プログラム postgres)は、 Postgres
スーパユーザが (データベース名を引数として) ユーザシェルから直接実行することができます。
しかしながら、こうすることは postmaster/サイトに関連付けられた共有バッファプールやロックテーブル
を無視するので、マルチユーザのサイトではお勧めできません。
コマンドの書式での角括弧 (“[” と “]”) は、オプションのフレーズやキーワードを示しています。 大括弧(“{” と “}”)の中にあって、 縦棒 (“|”) を含んでいるものは、 どれかひとつを選ばなくてはいけないことを示しています。
例では、丸括弧 (“(” と “)”) を ブール表現のグループ化に使っています。 “|” はブール型演算子 OR です。
例では色々なアカウントやプログラムから実行されたコマンドを表示しています。 root アカウントから実行されたコマンドは、 前に“>” がつきます。 Postgres のスーパーユーザのアカウントから 実行されたコマンドは、前に “%” がつき、 権限のないユーザのアカウントから実行されたコマンドは “$” が前につきます。 SQL コマンドには、状況によって、“=>” がつく場合もあるし、プロンプトが無い 場合もあります。
NOTE: 執筆時点(Postgres v6.5)で、 ドキュメントセットを通じて、 印を付けるコマンド群にたしての記述には一貫性があるわけではありません。 問題点は ドキュメンテーションメーリングリスト へレポートしてください。
Author: Written by Thomas Lockhart on 1998-10-22.
The PostgreSQL Global Development Team は Postgres ソフトウェアの コードツリーを、無保証で動作やパフォーマンスに関しては責任を持たないパ ブリックサービスとして提供しています。しかし、執筆時点においては：
このステートメントの筆者は regression テストでカバーされていない、 あるいは、最近から現在にかけて Postgres のバージョンで使われているその 他の分野においても、2000 年問題 に関するいかなる報告も受けておりません。 もし、問題が存在するなら、インストールされた基やサポートメーリングリス ト上のユーザの活発な参加で、その問題について知らされることを期待してい ました。
筆者の認識の限りでは、2桁の年で指定される日付についての Postgres の実装は、現在のユーザガイ ドのデータ型の章で文書化されています。2桁の年について、重要な 別れ目の年は 2000 年ではなく、1970 年です。例えば、“70-01-01” は “1970-01-01” と解釈されますが、“69-01-01” は “2069-01-01” と解釈 されます。
OSに潜む「現在時間」の取得に関する 2000年問題は、 Postgres の 2000年問題へも明らかに伝搬します。
Postgres95 is Copyright 1994-5 by the Regents of the University of California. Permission to use, copy, modify, and distribute this software and its documentation for any purpose, without fee, and without a written agreement is hereby granted, provided that the above copyright notice and this paragraph and the following two paragraphs appear in all copies.
In no event shall the University of California be liable to any party for direct, indirect, special, incidental, or consequential damages, including lost profits, arising out of the use of this software and its documentation, even if the University of California has been advised of the possibility of such damage.
The University of California specifically disclaims any warranties, including, but not limited to, the implied warranties of merchantability and fitness for a particular purpose. The software provided hereunder is on an "as-is" basis, and the University of California has no obligations to provide maintainance, support, updates, enhancements, or modifications.
UNIX is a trademark of X/Open, Ltd. Sun4, SPARC, SunOS and Solaris are
trademarks of Sun Microsystems, Inc. DEC, DECstation, Alpha AXP and ULTRIX
are trademarks of Digital Equipment Corp. PA-RISC and HP-UX are trademarks
of Hewlett-Packard Co. OSF/1 is a trademark of the Open Software Foundation.
この章は、もとは Stefan Simkovics' Master's Thesis (Simkovics, 1998) の一部として出たものです。SQL は最も人気のあるリレーショナル問い合わせ言語となりました。 “SQL”という名前は Structured Query Language の省略です。 1974 年、Donald Chamberlin などが、IBM リサーチで SEQUEL 言語 (Structured English Query Language) を定義しました。 この言語は最初 SEQUEL-XRM と呼ばれる IBM のプロトタイプに 1974-75年に実装されました。 1976-77年には、SEQUEL/2 と呼ばれる SQUEL の改訂版が定義され、 その名前がその後 SQL と変更されました。
システムR と呼ばれる新しいプロトタイプが IBM で 1977 年に開発されました。 システム R は SEQUEL/2 (現在の SQL) の大きなサブセットを実装し、 このプロジェクトで多くの変更が SQL になされました。 システム R は IBM 内部のサイトと、またいくつかの選択されたカスタマーサイトのとの両方の、 多くのユーザサイトにインストールされました。 そのようなユーザサイトへ首尾よく受け入れられたおかげで、 システム R のテクノロジーをベースとして SQL言語を実装した商用製品を IBM は開発し始めました。
その後数年の間に IBM や多くのその他のベンダーが SQL製品 をアナウンスしました。それには SQL/DS (IBM)、 DB2 (IBM)、 ORACLE (Oracle Corp.)、 DG/SQL (Data General Corp.)、 SYBASE (Sybase Inc.) のようなものがありました。
SQL は現在公式なスタンダードにもなっています。 1982 年には、アメリカ規格協会 (American National Standards Institute, ANSI) がリレーショナル言語の標準への提案を展開させるように、 データベース委員会 X3H2 を設立しました。 この提案は 1986 年に批准され、本質的には SQL の IBM 方言から成っています。 1987 年にはこの ANSI 標準は、 国際標準化機構(International Organization for Standardization, ISO)によって 国際標準としても容認されました。 SQL のこのオリジナル標準バージョンは、 しばしば非公式に "SQL/86" と呼ばれています。 1989 年にはオリジナルの標準は拡張されました。 そしてこの新しい標準はしばしば、これもまた非公式ですが、 "SQL/89" と呼ばれています。 同じく 1989 年に、Database Language Embedded SQL (ESQL)と呼ばれる関連した標準が開発されました。
ISO と ANSI の委員会は、 非公式にSQL2 やSQL/92として呼ばれる、
オリジナルの標準バージョンを大いに拡張したバージョンの定義に、 何年もの作業を続けています。
このバージョンは 1992 年の後半に採用された標準 - "International Standard
ISO/IEC 9075:1992, Database Language SQL" - となりました。 SQL/92 は、
普通 "標準 SQL" と言う時に意味するバージョンです。 SQL/92 の詳細は Date
and Darwen, 1997 にあります。 このドキュメントを書いている時点で、
SQL3
として呼ばれる 新しい標準が開発途中であります。 SQL を Turing-complete
言語、 すなわちすべての計算可能問い合わせ (例えば再帰的問い合わせ)を可能となるように、
計画されています。 これは非常に複雑なタスクで、これによってその新しい標準の完成は
1999 年以前には期待できません。
リレーショナルデータベース は、 ユーザから 表の集まり (そして表以外何もない) として 理解されるデータベースです。 表は行と列から成っており、各行はひとつのレコードを表し、 各列は表に含まれるレコードの属性を表すものです。 店と部品データベースでは 3つの表から成るデータベースの例を示しています。
PART は部品の数(PNO)と名前(PNAME)と価格(PRICE)を保存する表です。
SELLS はどの店(SNO)がどの部品(PNO)を販売したかの情報を保存します。 ある意味でそれは他の2つの表を結びつける役目をしています。
SUPPLIER SNO | SNAME | CITY SELLS SNO | PNO -----+---------+-------- -----+----- 1 | Smith | London 1 | 1 2 | Jones | Paris 1 | 2 3 | Adams | Vienna 2 | 4 4 | Blake | Rome 3 | 1 3 | 3 4 | 2 PART PNO | PNAME | PRICE 4 | 3 -----+---------+--------- 4 | 4 1 | Screw | 10 2 | Nut | 8 3 | Bolt | 15 4 | Cam | 25
表に対するSQLの操作は既に定義したのと同様ですが、
その定義には後で触れることとして、そのまえに、リレーショナルモデル
のについて考察してみましょう。
ドメインの直積 D1, D2, ... Dk, を D1 D2 ... Dk と表すと、 これはすべての k タプルの集合 v1, v2, ... vk, ここで v1 ∈ D1, v1 ∈ D1, ... vk ∈ Dk.
例えば、以下の場合 k=2, D1={0,1} and D2={a,b,c} then D1D2 is {(0,a),(0,b),(0,c),(1,a),(1,b),(1,c)}.
ひとつのリレーションは1あるいは複数のドメインの直積からなる部分集合です。 R ⊆ D1 D2 ... Dk.
例えば {(0,a),(0,b),(1,a)}というのがひとつのリレーションです。 これは上で触れたように、実際は以下の物の部分集合となります。 D1D2
このリレーションの項をタプルと呼ばれます。ある直積の各関係は D1D2 ... Dk k個の成分数を持つといい、 したがってk-タプルの集合となります。
リレーションは表に表されます。(すでにやっています。 店と部品データベースを思い出してください。) すべてのタプルが行で表され、 すべての列はひとつのタプルの1要素に対応します。 (属性と呼ばれる)名前をカラムに与えることで、 リレーションスキームの定義となります。
リレーションスキーム R は、属性 A1, A2, ... Ak の有限集合となります。 各属性Aiについて Diというドメインがあり、 1 <= i <= k です。 そこから属性の値は取り出されます。 しばしばリレーションスキームを R(A1, A2, ... Ak) と書きます。
NOTE: リレーションスキーム は一種のテンプレートですが、 リレーション は リレーションスキーム のひとつのインスタンスとなります。 リレーションはタプルで構成されます(そしてそれによって 表として見ることができます)。 リレーションスキームはそうではありません。
リレーショナル計算 これは論理表記で、 問い合わせは答えのタプルが満たすべき論理制約を式にすることで表現されます。
射影(PROJECT) (π): 指定した属性 (カラム) を リレーションから引き出します。 R は属性Xを含む リレーションにしてください。 πX(R) = {t(X) ? t ∈ R}, t(X) は タプルtの 属性Xの値を示します。
直積(PRODUCT) (×): 2つのリレーションの直積を作ります。 R は要素数 k1 の表で、 S は要素数 k2 としてください。 R × S はすべての k1 + k2-タプルの集合で、 R のタプルから最初の k1 要素と、 S のタプルの最後の k2 要素です。
結合(UNION) (∪): 2つの表の集合論的和をとります。 表R と S (両方とも同じ次数でなくてはなりません)を与えられた 和 R ∪ S は R か S、もしくは両方に 属するタプルの集合です。
積(INTERSECT) (∩): 2つの表の集合論的積を作ります。 表R と S を与えられた R ∪ S は R と S に属するタプルの集合です。 これもまた R と S が 同じ次数でなくてはなりません。
差(DIFFERENCE) (− もしくは ?): 2つの表の差集合を作ります。 R と S は、 これもまたおなじ次元の2つの表となります。 R - S は R に属しますが、Sには属さない タプルの集合となります。
結合(JOIN) (Π): 2つの表をその共通属性で結合します。 R は属性A,B, C を持つ表にします。 また S は属性C,D E を持つ表にします。 両方のリレーションに共通な属性は、属性 C だけとなります。 R Π S = πR.A,R.B,R.C,S.D,S.E(σR.C=S.C(R × S))。 ここでは何をしているのでしょうか? まず最初に直積 R S を計算します。 そして共通する属性 C が等しい値となる (σR.C = S.C) タプルを選択します。 ここでは属性 C を2度含む表があり、 重複したカラムを射影することで修正します。
結合に必要なステップを評価することから生じた表を見てみることにしましょう。 次の2つの表が与えられているとします。
R A | B | C S C | D | E ---+---+--- ---+---+--- 1 | 2 | 3 3 | a | b 4 | 5 | 6 6 | c | d 7 | 8 | 9
R × S A | B | R.C | S.C | D | E ---+---+-----+-----+---+--- 1 | 2 | 3 | 3 | a | b 1 | 2 | 3 | 6 | c | d 4 | 5 | 6 | 3 | a | b 4 | 5 | 6 | 6 | c | d 7 | 8 | 9 | 3 | a | b 7 | 8 | 9 | 6 | c | dσR.C=S.C(R × S) の選択によって、次のものが得られます。
A | B | R.C | S.C | D | E ---+---+-----+-----+---+--- 1 | 2 | 3 | 3 | a | b 4 | 5 | 6 | 6 | c | d重複したカラム S.C を削除するために、次の操作で射影を取ります。 πR.A,R.B,R.C,S.D,S.E(σR.C=S.C(R × S)) そして次のものが得られます。
A | B | C | D | E ---+---+---+---+--- 1 | 2 | 3 | a | b 4 | 5 | 6 | c | d商(DIVIDE) (÷): R は 属性 A,B,C,D を持つ表とします。そして S が属性 C,D を持つ表だとします。 そこで差を次のように定義します。 R ÷ S = {t ? ∀ ts ∈ S ∃ tr ∈ R これは、 tr(A,B)=t∧tr(C,D)=ts} ここで tr(x,y) は 要素x と y のみから成る 表Rのタプルを示します。 タプル t は、リレーションR の要素A と B のみから 成ることに注意してください。
次のような表が与えられて、
R A | B | C | D S C | D ---+---+---+--- ---+--- a | b | c | d c | d a | b | e | f e | f b | c | e | f e | d | c | d e | d | e | f a | b | d | eR ÷ S で次のものを得ます。
A | B ---+--- a | b e | dリレーショナル代数のもっと詳しい記述や定義については、 [Ullman, 1988] または [Date, 1994] を参照してください。
データベースからデータを取り出すことができるように、 すべてのリレーショナル演算子は式にすることを思い出して下さい。 前のセクション(リレーショナルデータモデルでの操作) からの例に戻ってみましょう。 ここでは、だれかが部品ネジを売っている すべての店の名前を知りたいとしていました。 この問いには、リレーショナル代数を使い、次の操作で答えることができます。
πSUPPLIER.SNAME(σPART.PNAME='Screw'(SUPPLIER Π SELLS Π PART))このような操作を問い合わせと呼びます。 もし例の表(店と部品データベース) に対する上記問い合わせを評価するなら、 次のような結果を得ることになるでしょう。
SNAME ------- Smith Adams
タプルリレーショナル計算 (TRC)、 変数はタプルを表します。
例 リレーショナル代数を使った問い合わせ の問い合わせに TRC を使って答えるなら、 次のような問い合わせを組み立てます。
{x(SNAME) ? x ∈ SUPPLIER ∧ \nonumber
∃y ∈ SELLS ∃z ∈ PART (y(SNO)=x(SNO) ∧ \nonumber
z(PNO)=y(PNO) ∧ \nonumber
z(PNAME)='Screw')} \nonumber
店と部品データベース からの表に対する問い合わせを評価すると、
これもまた リレーショナル代数を使った問い合わせ
と同じ結果となります。
リレーショナル計算に基づく言語は、 リレーショナル代数に基づく言語よりも、
"ハイレベル" もしくは "宣言的(more declarative)" と言われることがあります。
これは、代数が (部分的に) 操作の順番を指定するのに対して、 計算ではコンパイラやインタープリタが
もっと効率的な評価の順序を決めるように残しているからです。
算術能力: SQL では、 比較と同様に、算術演算を伴うことができます。例えば、 A < B + 3. + やその他の算術演算子はリレーショナル代数やリレーショナル計算には 現われないことに注意してください。
割当と出力のコマンド: 問い合わせによって作成されたリレーションを出力したり、 あるリレーション名に計算されたリレーションを割り当てることができます。
集計関数: average, sum, max などといった操作を、ひとつの数量として得るために リレーションのカラムとして適用することができます。
SELECT [ALL|DISTINCT]
{ * | expr_1 [AS c_alias_1] [, ...
[, expr_k [AS c_alias_k]]]}
FROM table_name_1 [t_alias_1]
[, ... [, table_name_n [t_alias_n]]]
[WHERE condition]
[GROUP BY name_of_attr_i
[,... [, name_of_attr_j]] [HAVING condition]]
[{UNION [ALL] | INTERSECT | EXCEPT} SELECT ...]
[ORDER BY name_of_attr_i [ASC|DESC]
[, ... [, name_of_attr_j [ASC|DESC]]]];
ここでSELECT 文の複雑な文法をいくつかの例をつけて説明します。 例で使われる表は
店と部品データベース
で定義されます。
表 PART から、属性 PRICE が 10 より大きいすべてのタプルを取り出すには、 次のような問い合わせを組み立てます。
SELECT * FROM PART WHERE PRICE > 10;and get the table:
PNO | PNAME | PRICE -----+---------+-------- 3 | Bolt | 15 4 | Cam | 25SELECT 文で "*" を使うことで、表からすべての属性を集めます。 もし表 PART から、属性 PNAME と PRICE だけを取り出したいなら、 次の記述を使います。
SELECT PNAME, PRICE FROM PART WHERE PRICE > 10;このケースでは、結果は次のようになります。
PNAME | PRICE --------+-------- Bolt | 15 Cam | 25SQL の SELECT はリレーショナル代数の "射影" に 相当するもので、"選択" ではありません。 (詳細はリレーショナル代数を参照してください。)
WHERE 句にある制約は、キーワード OR,AND,NOT を使うことで、 論理的に結合することができます。
SELECT PNAME, PRICE FROM PART WHERE PNAME = 'Bolt' AND (PRICE = 0 OR PRICE < 15);は次のような結果を導出します。
PNAME | PRICE --------+-------- Bolt | 15算術演算をターゲットリストや WHERE 句に使うことができます。 例えば、もし2つの部品を手にいれるにはいくらかかるかを知りたいなら、 次のような問い合わせを使えます。
SELECT PNAME, PRICE * 2 AS DOUBLE FROM PART WHERE PRICE * 2 < 50;そして次の結果を得ます。
PNAME | DOUBLE --------+--------- Screw | 20 Nut | 16 Bolt | 30キーワード AS の後の DOUBLE という語は、 2番目のカラムの新しいタイトルとなることに注意してください。 このテクニックは、ターゲットリストのすべての要素に使うことができ、 新しいタイトルを結果のカラムに割り当てることができます。 この新しいタイトルはしばしばエイリアス(alias)と呼ばれます。 エイリアスは問い合わせの残りで使うことはできません。
3つの表 SUPPLIER,PART,SELLS をそれらの共通の属性にわたって結合するには、 次の記述を組み立てます。
SELECT S.SNAME, P.PNAME FROM SUPPLIER S, PART P, SELLS SE WHERE S.SNO = SE.SNO AND P.PNO = SE.PNO;そして次の表を結果として得ます。
SNAME | PNAME -------+------- Smith | Screw Smith | Nut Jones | Cam Adams | Screw Adams | Bolt Blake | Nut Blake | Bolt Blake | CamFROM 句で、すべてのリレーションに対してエイリアス名を出しました。 というのは、リレーション間で共通の名前の属性 (SNO と PNO) があったからです。 これで共通の名前の属性を、 単にエイリアス名をつけた属性名にドットをつけたものを頭につけて、 区別することができます。 結合は 内結合 で示されるのと同じ方法で計算されます。 最初は直積 SUPPLIER PART SELLS が得られます。 ここで WHERE 句で与えられた条件を満たすタプルのみが選択されます (すなわち、共通の名前がついた属性は等しくなくてはいけません)。 最後に S.SNAME と P.PNAME 以外のすべてのカラムを吐き出します。
もし表 PART のすべての部品のコストの平均を知りたければ、 次の問い合わせを使います。
SELECT AVG(PRICE) AS AVG_PRICE FROM PART;結果は次の通りです。
AVG_PRICE ----------- 14.5もし表 PART にどれだけの部品があるかを知りたければ、 次の記述を使います。
SELECT COUNT(PNO) FROM PART;そして次の値を得ます。
COUNT ------- 4
タプルをグループに分割することは、 キーワード GROUP BY に続けて グループを定義する属性のリストを使うことで行なわれます。 もし GROUP BY A1, ?, Ak というものがあれば、 2つのタプルが同じグループにあるといったように、 すべての属性 A1, ?, Ak に一致すれば、リレーションをグループに分割します。
もし各店がいくつの部品を売ったかを知りたければ、 次のような問い合わせを組み立てます。
SELECT S.SNO, S.SNAME, COUNT(SE.PNO) FROM SUPPLIER S, SELLS SE WHERE S.SNO = SE.SNO GROUP BY S.SNO, S.SNAME;そして次の結果を得ます。
SNO | SNAME | COUNT -----+-------+------- 1 | Smith | 2 2 | Jones | 1 3 | Adams | 2 4 | Blake | 3ここで何が起こっているかを見てみましょう。 まず、表 SUPPLIER と SELLS の結合が得られます。
S.SNO | S.SNAME | SE.PNO -------+---------+-------- 1 | Smith | 1 1 | Smith | 2 2 | Jones | 4 3 | Adams | 1 3 | Adams | 3 4 | Blake | 2 4 | Blake | 3 4 | Blake | 4次に 属性 S.SNO と S.SNAME の両方に一致するすべてのタプルをまとめて、 タプルをグループに分割します。
S.SNO | S.SNAME | SE.PNO -------+---------+-------- 1 | Smith | 1 | 2 -------------------------- 2 | Jones | 4 -------------------------- 3 | Adams | 1 | 3 -------------------------- 4 | Blake | 2 | 3 | 4この例では、4つのグループを得ました。そしてここで集計演算子 COUNT を すべてのグループに適用して、上で与えられた問い合わせの全体の結果を導出します。
もし1個より多くの部品を売っている店のみが欲しければ、 次の問い合わせを使います。
SELECT S.SNO, S.SNAME, COUNT(SE.PNO) FROM SUPPLIER S, SELLS SE WHERE S.SNO = SE.SNO GROUP BY S.SNO, S.SNAME HAVING COUNT(SE.PNO) > 1;そして次のようになります。
SNO | SNAME | COUNT -----+-------+------- 1 | Smith | 2 3 | Adams | 2 4 | Blake | 3
もし 'Screw' という名前の部品よりも高い価格のすべての部品を知りたければ、 次のような問い合わせを行ないます。
SELECT * FROM PART WHERE PRICE > (SELECT PRICE FROM PART WHERE PNAME='Screw');結果は次のようになります。
PNO | PNAME | PRICE -----+---------+-------- 3 | Bolt | 15 4 | Cam | 25上の問い合わせを見てみると、キーワード SELECT を2回見ることができます。 問い合わせの頭の方にある最初のひとつ - それを外部 SELECT と呼ことにします - と WHERE 句でネストした問い合わせを始めているもの - これを内部 SELECT と 呼ぶことにします。 外部 SELECT のすべてのタプルのために、 内部 SELECT が評価されなくてはなりません。 すべての評価の後、'Screw' という名前のタプルの価格を知ることができ、 そして実際のタプルの価格が大きいかどうかをチェックすることができます。
もし部品を何も売っていない店を知りたければ (例えば、これらの店をデータベースから削除することができるように)、 次のように使います。
SELECT * FROM SUPPLIER S WHERE NOT EXISTS (SELECT * FROM SELLS SE WHERE SE.SNO = S.SNO);私達の例では、すべての店が少なくとも1つは部品を売っているので、 結果は空になります。 内部 SELECT の WHERE 句の名かで、 外部 SELECT からの S.SNO を使っていることに注意してください。 上記のように、副問い合わせは外部問い合わせのすべてのタプルで評価されます。 つまり、S.SNO の値は常に外部 SELECT の実際のタプルから来ていることになります。
次の問い合わせは 結合(UNION) のものです。
SELECT S.SNO, S.SNAME, S.CITY FROM SUPPLIER S WHERE S.SNAME = 'Jones' UNION SELECT S.SNO, S.SNAME, S.CITY FROM SUPPLIER S WHERE S.SNAME = 'Adams';次のような結果になります。
SNO | SNAME | CITY -----+-------+-------- 2 | Jones | Paris 3 | Adams | Vienna積(INTERSECT)のための例です。
SELECT S.SNO, S.SNAME, S.CITY FROM SUPPLIER S WHERE S.SNO > 1 INTERSECT SELECT S.SNO, S.SNAME, S.CITY FROM SUPPLIER S WHERE S.SNO > 2;結果は以下の通りになります。
SNO | SNAME | CITY -----+-------+-------- 2 | Jones | Paris 両方の問い合わせのから戻る唯一のタプルは $SNO = 2$ のものだけです。最後に EXCEPT の例です。
SELECT S.SNO, S.SNAME, S.CITY FROM SUPPLIER S WHERE S.SNO > 1 EXCEPT SELECT S.SNO, S.SNAME, S.CITY FROM SUPPLIER S WHERE S.SNO > 3;結果は次の通り。
SNO | SNAME | CITY -----+-------+-------- 2 | Jones | Paris 3 | Adams | Vienna
CREATE TABLE table_name (name_of_attr_1 type_of_attr_1 [, name_of_attr_2 type_of_attr_2 [, ...]]);
店と部品データベース で定義されている表を作るには、 次のような SQL 文が使われます。
CREATE TABLE SUPPLIER (SNO INTEGER, SNAME VARCHAR(20), CITY VARCHAR(20));
CREATE TABLE PART (PNO INTEGER, PNAME VARCHAR(20), PRICE DECIMAL(4 , 2));
CREATE TABLE SELLS (SNO INTEGER, PNO INTEGER);
SMALLINT: 符号つきハーフワードバイナリ整数 (15 ビット精度).
DECIMAL (p[,q]): p 桁の精度で、小数点以下が q とみなしてパックされた符号つき小数。 (15 ≧ p ≧ qq ≧ 0). もし q が省略されれば、0とみなされます。
FLOAT: 符号つきダブルワード浮動小数
CHAR(n): 長さnの 固定長文字列
VARCHAR(n): 可変長文字列で、最大長 n文字。
SQL でインデックスを作るには、 CREATE INDEX コマンドが使われます。 文法は以下のようになります。
CREATE INDEX index_name ON table_name ( name_of_attribute );
I という名前のインデックスをリレーション SUPPLIER の属性 SNAME 上に作るには、 次のような記述を使います。
CREATE INDEX I ON SUPPLIER (SNAME);作成されたインデックスは、自動的に維持されます。 つまり、新しいタプルがリレーション SUPPLIER に挿入されるといつも、 インデックス I は適合されるのです。 インデックスが存在する時にユーザが認識できる唯一の変化は スピードの増加だけなことに注意してください。
ビューには、物理的に分離されて区別できる保存データを持ちません。 代わりに、システムはシステムカタログのどこかに ビュー表の定義 (つまり、ビューを実現するために、物理的に保存された基本表へ どのようにアクセスすればよいかについてのルール)を保存します (システムカタログ を参照してください)。 ビュー表の実装への異なるテクニックについての議論は、 SIM98 を参照してください。
SQL では CREATE VIEW コマンドがビューを定義するのに使われます。 文法は以下のようになります。
CREATE VIEW view_name AS select_stmtここで、select_stmt は 選択 で定義された、 有効な選択文です。 select_stmt は ビューが作成された時には実行されないことに注意してください。 単に システムカタログに保存され、 ビューに対する問い合わせが起こった時に実行されます。
次のようなビューの定義が与えられたとしましょう (また店と部品データベースからの表です)。
CREATE VIEW London_Suppliers AS SELECT S.SNAME, P.PNAME FROM SUPPLIER S, PART P, SELLS SE WHERE S.SNO = SE.SNO AND P.PNO = SE.PNO AND S.CITY = 'London';ここでこの 仮想リレーション London_Suppliers を他の基本表であるかのように使うことができます。
SELECT * FROM London_Suppliers WHERE P.PNAME = 'Screw';は次のような表を返します。
SNAME | PNAME -------+------- Smith | Screwこの結果を計算するには、データベースシステムが 基本表 SUPPLIER,SELLS,PART への 隠れたアクセスを まずしなくてはなりません。 それら基本表に対して、ビューの定義に与えられている問い合わせを 実行することでそうします。 その後、付加的な制約 (ビューへの問い合わせで与えられます) が結果の表を得るために用いられます。
DROP TABLE table_name;SUPPLIER 表を削除するには、次の記述を使います。
DROP TABLE SUPPLIER;DROP INDEX コマンドがインデックスを削除するのに使われます。
DROP INDEX index_name;最後に DROP VIEW コマンドを使って、与えられたビューを削除します。
DROP VIEW view_name;
INSERT INTO table_name (name_of_attr_1 [, name_of_attr_2 [,...]]) VALUES (val_attr_1 [, val_attr_2 [, ...]]);リレーション SUPPLIER ( 店と部品データベースから ) へ最初のタプルを挿入するには、次の記述を使います。
INSERT INTO SUPPLIER (SNO, SNAME, CITY) VALUES (1, 'Smith', 'London');リレーション SELLS へ最初のタプルを挿入するには、次を使います。
INSERT INTO SELLS (SNO, PNO) VALUES (1, 1);
UPDATE table_name SET name_of_attr_1 = value_1 [, ... [, name_of_attr_k = value_k]] WHERE condition;リレーション PART で、部品 'Screw' の属性 PRICE の値を変更するには、 次のようにつかいます。
UPDATE PART SET PRICE = 15 WHERE PNAME = 'Screw';タプルの属性 PRICE で名前が 'Screw' の新しい値はこれで 15 となります。
DELETE FROM table_name WHERE condition;表 SUPPLIER の 'Smith' と呼ばれる店を削除するには、 次のような記述が使われます。
DELETE FROM SUPPLIER WHERE SNAME = 'Smith';
ホスト言語で書かれたアプリケーションから簡単に データベースへアクセスを行ないたいと思います。 (例えば、グラフィカルユーザインターフェイスを持つチケット予約システムが C で書かれており、どのチケットがまだ残っているかが データベースに保管されていて、それに埋め込み SQLを使って アクセスすることができます。)
例 選択 を通して見てみると、 問い合わせの結果は非常に多くの場合タプルの集合であることに気づくでしょう。 多くのホスト言語は集合の操作用にデザインされていませんので、 SELECT文から返されるタプルの集合の各単一のタプルへアクセスする メカニズムが必要となります。 このメカニズムは カーソル を宣言することで提供されます。 それから FETCH コマンドを使ってひとつのタプルを取り出し、 そのカーソルを次のタプルにセットすることができます。
埋め込み SQL の詳細な論議に関しては、 [Date and
Darwen, 1997] や [Date, 1994] や [Ullman,
1988] を参照してください。
ユーザのフロントエンドアプリケーション(すなわち、psqlプログラム)、
1つ以上のバックエンドデータベースサーバ(postgresプロセス自身)。
libpqライブラリを使うと、1つのフロントエンドが バックエンドプロセスに対して複数の接続を行なうことができます。 しかしながら、フロントエンドのアプリケーションは相変わらずシングルスレッドのプロセスです。 マルチスレッドのフロントエンド/バックエンド接続は libpqにおいては現在 サポートされていません。このアーキテクチャでは、フロントエンドアプリケーションは どこで動いていてもよいにもかかわらず、 postmasterとバックエンドは、常に同一のマシン (データベースサーバ)で動作しなければならないことを暗に示しています。 これは覚えておく必要があります。それは、クライアントマシン上でアクセスできるファイルは、 データベースサーバマシン上ではアクセスできない(または、異なったファイル名を使ってのみ アクセスできる)かもしれないからです。
postmasterと postgres サーバは、 Postgresの "スーパーユーザ" のユーザID
で動作することにも注意してください。Postgres の スーパーユーザは、特定のユーザ(すなわち
"postgres" という名前のユーザ) である必要はありません。さらに Postgres
の スーパーユーザは、明らかに UNIX のスーパーユーザ ("root") であってはなりません。
いずれのケースにおいても、データベースに関連するすべてのファイルは、 この
Postgres スーパーユーザに属している必要があります。
新しいユーザが Postgres で作業を始める方法です。Postgres を使うのに必要なステップのいくつかは、 Postgres のユーザの誰にでもできますが、 サイトのデータベース管理者がやらなくてはならないものもあります。 このサイト管理者はソフトウェアをインストールした人で、 データベースのディレクトリを作り、 postmaster プロセスをスタートさせた人です。 この人は UNIX のスーパーユーザ(“root”)である必要はありません。 特別なアカウントや権限なしに Postgres をインストールして使うことができます。
もし Postgres をインストールしているなら、 インストールの説明について管理者ガイドを参照して、 インストールが完了したらこのガイドに戻ってください。
このマニュアルを通じて、 “%” 文字で始まる例は UNIX のシェルプロンプトにタイプするコマンドです。
Postgres はクライアント/サーバアプリケーションです。 ユーザにとってみれば、インストールの一部のクライアント(クライアントアプリケーションの 例は対話モニタのpsqlです)にアクセスするだけです。 話を単純にするため、Postgres がディレクトリ /usr/local/pgsql にインストールされたものとします。 ですから、ディレクトリ /usr/local/pgsql を見たら、 それを Postgres が実際にインストールされた ディレクトリの名前と置き換えてください。 すべての Postgres のコマンドは ディレクトリ /usr/local/pgsql にインストールされます。 ですので、このディレクトリをシェルのコマンドパスに加えてください。 もし csh や tcsh のような バークレイ C シェル系を使っているのなら、 ホームディレクトリの .login ファイルに
% set path = ( /usr/local/pgsql/bin path )を加えて下さい。 sh や ksh や bash といった Bourne shell 系を使っているなら、 ホームディレクトリの .profile ファイルに
% PATH=/usr/local/pgsql/bin:$PATH % export PATHを加えてください。 これからは、Postgres の bin ディレクトリが パスに加えられているものとします。 さらに、このドキュメントを通じて、“シェル変数の設定” や “環境変数の設定” を頻繁に使います。 もし前の段落で検索パスを変更することが完全に理解できていないなら、 これから先に進む前に、使っているシェルの UNIX マニュアルページを 調べておいてください。
もしサイトの管理者がデフォルトの方法で設定をしていなかったなら、 もう少しやることが増えます。
例えば、データベースサーバマシンがリモートのマシンなら、 環境変数 PGHOST
をデータベースサーバマシンにセットする必要があります。 環境変数 PGPORT
もセットしなくてはなりません。 最後にひとつ。 もしアプリケーションプログラムをスタートしようとしていて、
postmasterに接続できないと訴えてきたら、 すぐに自分の環境が適切にセットアップされているかどうか確かめるように
サイト管理者に確かめてください。
Postgres バージョン 6.3 の時点で、 2つの違ったスタイルの接続がサポートされています。 サイト管理者は TCP/IP ネットワーク接続を許可するようにしているか、 もしくはデータベースへのアクセスをローカル(同じマシン)の ソケット接続だけに制限するかしています。 データベースへの接続の問題に直面した時にこの選択は重要となります。
次のようなエラーメッセージを (psql や createdbのような) Postgres のコマンドから受け取ったとします。
% psql template1 Connection to database 'postgres' failed. connectDB() failed: Is the postmaster running and accepting connections at 'UNIX Socket' on port '5432'?もしくは
% psql -h localhost template1 Connection to database 'postgres' failed. connectDB() failed: Is the postmaster running and accepting TCP/IP (with -i) connections at 'localhost' on port '5432'?これは通常、(1) postmaster が走っていないか、 あるいは (2) 間違ったサーバホストに接続しようとしているかのどちらかの理由です。 次のようなエラーメッセージを受け取ったとします。
FATAL 1:Feb 17 23:19:55:process userid (2360) != database owner (268)これはサイト管理者が postmaster を 間違ったユーザでスタートさせたことを意味します。 Postgres のスーパーユーザで再スタートしてもらうように、 管理者に伝えましょう。
多くの Postgres のアプリケーションは、 指定されなければ、 データベース名があなたのコンピュータアカウントの名前と同じであると推測します。
もしデータベース管理者があなたのアカウントをデータベースの 作成権限なしにセットアップしたなら、 あなたのデータベースの名前を伝えているでしょう。 もしこのようなケースでしたら、 データベースの作成と削除のセクションをスキップして構いません。
% createdb mydbもしデータベースの作成に必要な権限を持っていないとすると、 次のようなメッセージを見るでしょう。
% createdb mydb WARN:user "your username" is not allowed to create/destroy databases createdb: database creation failed on mydb.Postgres は与えられたサイトに いくつでもデータベースを作ることができます。 そしてあなたが作ったデータベースのデータベース管理者は自動的にあなたになります。 データベースの名前はアルファベットで始まる文字で 32文字までの長さに 制限されています。 [訳注: 日本語のファイル名がサポートされているプラットフォームでは 日本語でもかまいません。]
LIBPQ サブルーチンライブラリを使った C プログラムを書きます。 これで SQL コマンドを C から送信して、 結果とステータスメッセージをプログラムに戻すことができます。 このインタフェイスは先の PostgreSQL プログラマーズガイド で説明されます。
% psql mydb次のメッセージの挨拶があります。
Welcome to the POSTGRESQL interactive sql monitor: Please read the file COPYRIGHT for copyright terms of POSTGRESQL type \? for help on slash commands type \q to quit type \g or terminate with semicolon to execute query You are currently connected to the database: template1 mydb=>このプロンプトは、ターミナルモニタがあなたの入力を待ち、 ターミナルモニタの整備するワークスペースに SQL の問い合わせをタイプすることができることを示します。 psql プログラムは バックスラッシュ文字 “\” で始まるエスケープコードに応答します。 例えば、Postgres SQLコマンドの 文法のヘルプが、次のようにタイプすることで得られます。
mydb=> \hワークスペースへの問い合わせの入力が終了したら、 ワークスペースの内容を Postgres サーバに 次のようにタイプして渡すことができます。
mydb=> \gこれはサーバに問い合わせを処理しろと伝えます。 問い合わせをセミコロンで終端させると、 “\g” は必要ではありません。 psql は自動的に セミコロンで終端した問い合わせを処理します。 対話的に入力するかわりに 問い合わせをファイル(myFile としましょう)から読み込む場合には、 次のようにタイプしてください。
mydb=> \i fileNamepsql から抜けて UNIX に戻るには、
mydb=> \qとタイプすると、psql は終了してコマンドシェルに戻ります。 (他のエスケープコードについては、モニタのプロンプトで \h とタイプしてみてください。) 空白(すなわち、スペース、タブ、改行)は SQL 問い合わせの中で自由に使うことができます。 一行のコメントは “--” で表します。 ダッシュの後の行の最後まではすべて無視されます。 複数行のコメントと、行の中にいれるコメントは “/* ... */” で表します。
% destroydb mydbこのアクションはデータベースに関連するすべてのUNIX ファイルを削除し、 とりやめることはできませんので、 これは充分な注意を払って行ってください。
% cd /usr/local/pgsql/src/tutorial % psql -s mydb Welcome to the POSTGRESQL interactive sql monitor: Please read the file COPYRIGHT for copyright terms of POSTGRESQL type \? for help on slash commands type \q to quit type \g or terminate with semicolon to execute query You are currently connected to the database: postgres mydb=> \i basics.sql\i コマンドは指定したファイルから問い合わせを読み込みます。 -s オプションでシングルステップモードに入り、 バックエンドに問い合わせを送る前に一時停止するようになります。 この節にある問い合わせは basics.sql ファイルにあります。
psqlは、 システム情報を表示するさまざまな\dコマンドを持っています。
これらのコマンドの詳細は、 psql プロンプトで \? とタイプすることで出てくるリストを参考にしてください。
CREATE TABLE 天気 ( 街 varchar(80), 最低気温 int, -- 最低気温 最高気温 int, -- 最高気温 降水量 real, -- 降水量 日付 date );キーワードと識別子は大文字小文字を区別しないことに注意してください。 SQL92 で許されているように、 識別子は二重引用符で囲むことによって大文字小文字を区別することが できるようになります。 Postgres SQL は 通常のSQL の型である int, float, real, smallint, char(N), varchar(N), date, time, timestamp といった型と同時に、 一般的に有用な型や豊富なひとそろえの幾何学的な型をサポートしています。 後でも触れますが、Postgres は 任意の数のユーザ定義のデータ型でカスタマイズすることができます。 したがって、型の名前は SQL92標準でサポートが必要とされる特別な場合を除いて、 文法的なキーワードではありません。 これまで Postgres の作成コマンドは 従来の関係システムで表を作成するのに使われるコマンド まったく同じように見えます。 しかし、クラスには関係モデルの拡張であるプロパティを持つことが すぐに分かります。
INSERT INTO 天気
VALUES ('サンフランシスコ', 46, 50, 0.25, '11/27/1994')
また、フラットな(ASCIIの)ファイルから 大量のデータをロードするために copy
コマンドを使うこともできます。 これは一般的に高速になります。というのも、データは単一の原子的トランザクションとして
ターゲットのテーブルに直接読み込み(あるいは書き込み)するからです。 例としては以下のようになります。
COPY INTO 天気 FROM '/home/user/weather.txt' USING DELIMITERS '|';ここでソースファイルへのパス名は、バックエンドサーバがファイルを直接読み込むため、 クライアントのマシンではなく、バックエンドサーバのマシンで利用可能でなくてはなりません。
SELECT * FROM 天気;そして出力は次のようになるはずです。
+------------------+----------+----------+--------+------------+ |街 | 最低気温 | 最高気温 | 降水量 | 日付 | +------------------+----------+----------+--------+------------+ | サンフランシスコ | 46 | 50 | 0.25 | 11-27-1994 | +------------------+----------+----------+--------+------------+ | サンフランシスコ | 43 | 57 | 0 | 11-29-1994 | +------------------+----------+----------+--------+------------+ | ヘイワード | 37 | 54 | | 11-29-1994 | +------------------+----------+----------+--------+------------+任意の式をターゲットリストに指定することもできます。 例えば次のようにできます。
SELECT 街, (最高気温+最低気温)/2 AS 平均気温, 日付 FROM 天気;任意のブール型演算子 (andとorとnot) が問い合わせの制約句に許されています。 例えば次のようになります。
SELECT * FROM 天気 WHERE 街 = 'サンフランシスコ' AND 降水量 > 0.0;結果はこのようになります。
+------------------+----------+----------+--------+------------+ |街 | 最低気温 | 最高気温 | 降水量 | 日付 | +------------------+----------+----------+--------+------------+ | サンフランシスコ | 46 | 50 | 0.25 | 11-27-1994 | +------------------+----------+----------+--------+------------+最後の注釈として、選択の結果を 順番に並べる、 もしくは重複したインスタンスを削除する ように指定することができます。
SELECT DISTINCT 街 FROM 天気 ORDER BY 街;
SELECT * INTO TABLE temp FROM 天気;この形式では、 select intoコマンドのターゲットリストに 指定された属性の名前と型を持つ新しいクラス temp を作る createコマンドを暗に示しています。 それから、もちろん、その結果のクラスには 他のクラスに行うことのできる操作ならなんでもすることができるのです。
NOTE: これは概念的なモデルです。 実際の結合はもっと効率的な方法で実行されますが、 これはユーザには見えないところです。これは次のような問い合わせで行うことができます。
SELECT W1.街, W1.最低気温 AS 最低, W1.最高気温 AS 最高, W2.街, W2.最低気温 AS 最低, W2.最高気温 AS 最高 FROM 天気 W1, 天気 W2 WHERE W1.最低気温 < W2.最低気温 AND W1.最高気温 > W2.最高気温; +-----------------+------+------+------------------+------+------+ |街 | 最低 | 最高 | 街 | 最低 | 最高 | +-----------------+------+------+------------------+------+------+ |サンフランシスコ | 43 | 57 | サンフランシスコ | 46 | 50 | +-----------------+------+------+------------------+------+------+ |サンフランシスコ | 37 | 54 | サンフランシスコ | 46 | 50 | +-----------------+------+------+------------------+------+------+
NOTE: このような結合の意味は、 制約句が問い合わせで示したクラスの直積に定義された式が 真であるということです。 制約句が真である直積のインスタンスのために、 Postgres は ターゲットリストに指定された値を計算して返します。 Postgres SQLは そのような式の重複した値に何の意味も与えません。 これはPostgresが時に 同じターゲットリストを幾度か再計算することを意味します。 これはブール型の式が "or" でつながったときに度々起こります。 そのような重複を削除するには、 select distinct 文を使わなくてはなりません。このケースで、W1 と W2 は両方とも天気クラスのインスタンスの代用で、 両方の範囲ともにクラスのすべてのインスタンスをまたいでいます。 (多くのデータベースシステム用語として、 W1 と W2 は 範囲変数として知られています。) 問い合わせには任意の数のクラス名と代用を含めることができます。
UPDATE 天気 SET 最高気温 = 最高気温 - 2, 最低気温 = 最低気温 - 2 WHERE date > '11/28/1994';
DELETE FROM 天気 WHERE 街 = 'ヘイワード';ヘイワードのすべての天気レコードが削除されます。 次のような形式の問い合わせには注意してください。
DELETE FROM クラス名;制約句がないと、delete は 与えられたクラスのすべてのインスタンスを単に削除して空にします。 これを行う前にシステムは確認を取りません。
SELECT max(最低気温) FROM 天気;は許されますが、
SELECT 街 FROM 天気 WHERE 最低気温 = max(最低気温);はダメです。 しかし、多くの場合問い合わせを言い換えることで 望む結果を得ることができます。 ここでは副問い合わせを使います:
SELECT 街 FROM 天気 WHERE 最低気温 = (SELECT max(最低気温) FROM 天気);集計関数はまたgroup by句を持つことができます:
SELECT 街, max(最低気温) FROM 天気 GROUP BY 街;
CREATE TABLE cities ( name text, population float, altitude int -- (in ft) ); CREATE TABLE capitals ( state char2 ) INHERITS (cities);この場合、capitals のインスタンスは、親である cites インスタンスから全ての属性 ( cities、population、及び、altitude )を継承します。name のデータ型は text です。これは可変長 ASCII 文字列を表すPostgresネイティブのデータ型です。population のデータ型は floatで、倍精度浮動少数点のための Postgresネイティブのデータ型です。 State capitals は、さらにそれらの州を示す state という属性を持っています。Postgres は、ゼロ個以上のクラスを継承することが出来、そして問い合わせもクラスの中のすべてのインスタンス、またはクラスの中のすべてのインスタンスとその継承先も参照することが出来ます。
NOTE: 継承の階層構造は、方向性の有るらせん状グラフです。例えば、次の問い合わせは、標高が 500ft より高い全ての都市を探し出します：
SELECT name, altitude FROM cities WHERE altitude > 500; +----------+----------+ |name | altitude | +----------+----------+ |Las Vegas | 2174 | +----------+----------+ |Mariposa | 1953 | +----------+----------+一方、州都を含む全ての都市で 500ft より高い標高に位置しているの都市をを探すための問い合わせは、以下の様になります：
SELECT c.name, c.altitude FROM cities* c WHERE c.altitude > 500;これは、以下の結果を返します：
+----------+----------+ |name | altitude | +----------+----------+ |Las Vegas | 2174 | +----------+----------+ |Mariposa | 1953 | +----------+----------+ |Madison | 845 | +----------+----------+ここで、cities の後の “*”（アスタリスク）は、その問い合わせがすべての cities、およびその継承構造における cities 配下のすべてのクラスに対して実行されることを示しています。 既に紹介した数々のコマンド ( select や update、そして delete )は、その他のコマンド、例えば alter の様に、この“*”表記法をサポートします。
CREATE TABLE SAL_EMP ( name text, pay_by_quarter int4[], schedule text[][] );上記のクエリーは SAL_EMP というクラスに、text 型の name、従業員の４半期までにその従業員の給料を表す int4 型一次元配列の pay_by_quarter、従業員の１週間のスケジュールを表す text 型二次元配列の schedule を作成します。 幾つか挿入 (INSERT) をしてみます； 配列に添字をする場合、値を"[]"（ブラケット）に入れ、そしてコンマによってそれらを切り離すことに注意してください。もしあなたがC言語を知っているなら、これは構造体を初期化する構文と同じです。
INSERT INTO SAL_EMP
VALUES ('Bill',
'{10000, 10000, 10000, 10000}',
'{{"meeting", "lunch"}, {}}');
INSERT INTO SAL_EMP
VALUES ('Carol',
'{20000, 25000, 25000, 25000}',
'{{"talk", "consult"}, {"meeting"}}');
デフォルトでは、 Postgres は配列のために「１始まり」の番号付けを使います
− すなわち、ｎ個の要素が有る配列は、配列[1]から始まり配列[n]で終わります。
さあ、幾つかの問い合わせを SAL_EMP に対して行うことができるようになりました。
まず、一度に配列のひとつの要素にアクセスする方法をお見せしましょう。 以下の問い合わせは、給料が第２四半期に変化したその従業員の名前を検索します：
SELECT name FROM SAL_EMP WHERE SAL_EMP.pay_by_quarter[1] <> SAL_EMP.pay_by_quarter[2]; +------+ |name | +------+ |Carol | +------+以下の問い合わせは、すべての従業員の第3四半期の給料を検索します：
SELECT SAL_EMP.pay_by_quarter[3] FROM SAL_EMP; +---------------+ |pay_by_quarter | +---------------+ |10000 | +---------------+ |25000 | +---------------+さらに、配列あるいは部分配列の任意のスライスにアクセスすることができます。 以下の問い合わせは、Billさんの今週始め2日間のスケジュールの一番最初の項目だけを検索します。
SELECT SAL_EMP.schedule[1:2][1:1]
FROM SAL_EMP
WHERE SAL_EMP.name = 'Bill';
+-------------------+
|schedule |
+-------------------+
|{{"meeting"},{""}} |
+-------------------+
トリガーのような新しい機能を使うことにより、タイムトラベルが必要とされない場合（多くのユーザにとっては、このような場合がほとんどなのですが）に特にオーバーヘッドをかけることもなく、必要な場合にだけタイムトラベルの様な動作を行わせることができるようになります。 さらに情報が必要なときは、contrib ディレクトリにあるサンプルを見てください。
タイムトラベルは見捨てられています：: この章にあるタイムトラベルの残りの文章は、同じ目的を達成する新しい構文で書きなおされるまで、ずっとこのままです。何処かにボランティアはいませんか？ - thomas 1998-01-12Postgres は、タイムトラベルの概念をサポートします。この機能により、ユーザは時系列に沿った問合せを行うことができるようになります。例えば、現在のマリポサ市の人口を探す問い合わせは以下のようになります：
SELECT * FROM cities WHERE name = 'Mariposa'; +---------+------------+----------+ |name | population | altitude | +---------+------------+----------+ |Mariposa | 1320 | 1953 | +---------+------------+----------+Postgres は、現時点において正しいバージョンのマリポサ市のレコードを自動的に発見してくれます。また、この問い合わせに時刻（あるいは時間）の範囲を与えることが出来ます。例えば、マリポサ市の過去と現在の人口を見るために、以下の様な問い合わせをするとします：
SELECT name, population FROM cities['epoch', 'now'] WHERE name = 'Mariposa';"epoch" は、システムクロックの最初を示しています。
NOTE: UNIXシステムでは、この値は常に1/1/1970 の 0:00:00 GMTです。もしあなたが今までのすべての例題を実行してきたならば、上記の問い合わせは以下の結果を返します：
+---------+------------+ |name | population | +---------+------------+ |Mariposa | 1200 | +---------+------------+ |Mariposa | 1320 | +---------+------------+デフォルトでのタイムレンジの始まりはシステムが一番最初として表現する時間で、そしてデフォルトのタイムレンジの終了は現在の時間です、したがって上記のタイムレンジは [,] の様に省略することが出来ます。
Selected references and readings for SQL and Postgres.
The Practical SQL Handbook , Judity Bowman, Sandra Emerson, and Marcy Damovsky, ISBN: 0-201-44787-8, Addison-Wesley, 1997.
A Guide to the SQL Standard , C. J. Date and Hugh Darwen, ISBN: 0-201-96426-0, Addison-Wesley, 1997.
An Introduction to Database Systems , C. J. Date, Addison-Wesley, 1994.
Understanding the New SQL , Jim Melton and Alan R. Simon, ISBN: 1-55860-245-3, Morgan Kaufmann, 1993.
Principles of Database and Knowledge , Jeffrey D. Ullman, Computer Science Press .
The PostgreSQL Administrator's Guide , The PostgreSQL Global Development Group.
The PostgreSQL Developer's Guide , The PostgreSQL Global Development Group.
The PostgreSQL Programmer's Guide , The PostgreSQL Global Development Group.
The PostgreSQL Tutorial Introduction , The PostgreSQL Global Development Group.
The PostgreSQL User's Guide , The PostgreSQL Global Development Group.
Enhancement of the ANSI SQL Implementation of PostgreSQL , Stefan Simkovics, Department of Information Systems, Vienna University of Technology .
The Postgres95 User Manual , A. Yu and J. Chen, The POSTGRES Group , University of California, Berkeley CA.
Partial indexing in POSTGRES: research project , Nels Olson, ISSN: UCB Engin T7.49.1993 O676, University of California, Berkeley CA.
A Unified Framework for Version Modeling Using Production Rules in a Database System , L. Ong and J. Goh, ISSN: ERL Technical Memorandum M90/33, University of California, Berkeley CA.
The Postgres Data Model , L. Rowe and M. Stonebraker.
Generalized partial indexes , P. Seshadri and A. Swami, ISSN: Cat. No.95CH35724, IEEE Computer Society Press.
The Design of Postgres , M. Stonebraker and L. Rowe.
The Design of the Postgres Rules System, M. Stonebraker, E. Hanson, and C. H. Hong.
The Postgres Storage System , M. Stonebraker.
A Commentary on the Postgres Rules System , M. Stonebraker, M. Hearst, and S. Potamianos.
The case for partial indexes (DBMS) , M. Stonebraker.
The Implementation of Postgres , M. Stonebraker, L. A. Rowe, and M. Hirohama.
On Rules, Procedures, Caching and Views in Database Systems ,
M. Stonebraker and et al.