PostgreSQLはオープンソースのリレーショナルデータベース管理システム（RDBMS）です。オープンソースのデータベースとしては、MySQLと並んで最もよく使われています。この記事ではPostgreSQLの使い方をご紹介します。
エクスプローラからMicrosoft Windows用PostgreSQLのインストーラを右クリックしてコンテキストメニューを表示させる。メニューから「管理者として実行」をクリックする。
ユーザーアカウント制御のダイアログが表示されて「次のプログラムにこのコンピュータへの変更を許可しますか？」と聞かれるので、プログラム名がPostgreSQLのインストーラであることを確認して、「はい(Y)」ボタンをクリックする。
Windows用PostgreSQLはMicrosoft Visual C++ Redistributableが必要である。まだインストールされていなければ自動的にインストールされる。既にインストールされていれば、Microsoft Visual C++ Redistributableのインストールはスキップされる。
セットアップウィザードが表示されるので、「Next >」ボタンをクリックする。
インストールディレクトリを聞かれるので、任意のディレクトリを指定する。基本的にデフォルトのディレクトリでよい。ディレクトリを指定したら「Next >」ボタンをクリックする。
インストールするコンポーネントを聞かれるので、インストールするコンポーネントにチェックを入れる。基本的にデフォルトの状態のままでよい。コンポーネントを選択したら「Next >」ボタンをクリックする。
データベースのデータを保存するディレクトリを聞かれるので、任意のディレクトリを指定する。インストールディレクトリにProgram Files下のフォルダを指定した場合、データディレクトリのデフォルトもProgram Files下のフォルダになるが、ここはユーザのアクセスが制限される場所なので、Program Files下以外に変更した方がよい。
データディレクトリを指定したら「Next >」ボタンをクリックする。
データベース管理者とサービスのアカウント (postgres) のパスワードを聞かれるので、任意のパスワードを入力する。パスワードを入力したら「Next >」ボタンをクリックする。
PostgreSQLサーバが接続を受け付けるポート番号を聞かれるので、任意のポート番号を入力する。基本的にデフォルトの状態のままでよい。ポート番号を入力したら「Next >」ボタンをクリックする。
作成するデータベース・クラスタのロケールを聞かれるので、「C」を選択する。
ロケールを選択したら「Next >」ボタンをクリックする。
インストール設定が表示されるので、「Next >」ボタンをクリックする。
インストールの準備が整った旨が表示されるので、「Next >」ボタンをクリックする。
インストールが実行される。
インストールが完了した旨が表示される。PostgreSQLの追加ソフトウェアをインストールするためのツールである「スタックビルダ」を起動するかどうかのチェックがあるので、インストーラを終了させた後に起動させたければチェックを入れる。追加したいソフトウェアが無ければチェックを外してよい。
「Finish」ボタンをクリックすると、PostgreSQLのインストーラが終了する。
PostgreSQLのサービスを起動
# /etc/init.d/postgresql startPostgreSQLの起動状態を確認
# /etc/init.d/postgresql status
Running clusters: 9.1/mainPostgreSQLのサービスを停止
# /etc/init.d/postgresql stopCREATE ROLEはロールを作成するPostgreSQL SQL文である。作成したロールはDROP ROLE文で削除できる。
PostgreSQLでは、いわゆるユーザのことを「ロール」と呼ぶ。Oracleデータベースではユーザとロールは別のものであるが、PostgreSQLではそのような区別はない。
CREATE ROLE name WITH LOGIN PASSWORD 'password'PostgreSQL では配列が使える。ただし、Oracleでは配列は使えないので、SQLに互換性は無くなる。
生徒ごとに1学期、2学期、3学期の試験の点数を格納するテーブルを作成する例を示す。
CREATE TABLE exam_score (student_no INTEGER, score INTEGER[3])DROP ROLEはCREATE ROLE文で作成したロールを削除するPostgreSQL SQL文である。
PostgreSQLでは、いわゆるユーザのことを「ロール」と呼ぶ。Oracleデータベースではユーザとロールは別のものであるが、PostgreSQLではそのような区別はない。
ロールを削除する。指定したロールが存在しない場合はエラーになる。
DROP ROLE nameロールを削除する。指定したロールが存在しない場合でもエラーにならない。
DROP ROLE IF EXISTS nameGRANTはアクセス権限を付与するPostgreSQL SQL文である。付与したアクセス権限はREVOKE文で剥奪できる。
テーブル、ビューおよびシーケンスに対するSELECTを許可する。SELECT FOR UPDATE文を実行するには、SELECT権限に加えてUPDATE権限も必要である。
GRANT SELECT ON emp TO tsukaテーブルへのINSERTを許可する。
GRANT INSERT ON emp TO tsukaテーブルに対するUPDATEを許可する。SELECT FOR UPDATE文を実行するには、SELECT権限に加えてUPDATE権限も必要である。
GRANT UPDATE ON emp TO tsukaテーブルから行のDELETEを許可する。
GRANT DELETE ON tmp TO tsukaテーブル上のトリガの作成を許可する。
GRANT TRIGGER ON emp TO tsuka外部キー制約を作成する際、キーを参照することを許可する。外部キー制約を持つテーブルを作成するためには、被参照キーを持つテーブルに対してこの権限が必要である。
GRANT REFERENCES ON emp TO tsukaテーブルに対するすべての権限を許可する。
GRANT ALL PRIVILEGES ON emp TO tsukaREVOKEはGRANT文によって付与されたアクセス権限を剥奪するSQL文である。
テーブル、ビューおよびシーケンスに対するSELECTを禁止する。
REVOKE SELECT ON object FROM userテーブルへのINSERTを禁止する。
REVOKE INSERT ON table FROM userテーブルに対するUPDATEを禁止する。
REVOKE UPDATE ON table FROM userテーブルから行のDELETEを禁止する。
REVOKE DELETE ON table FROM userテーブル上のトリガの作成を禁止する。
REVOKE TRIGGER ON table FROM user外部キー制約を作成する際、キーを参照することを禁止する。
REVOKE REFERENCES ON table FROM userテーブルに対するすべての権限を剥奪する。
REVOKE ALL PRIVILEGES ON table FROM userSELECTはデータベースに照会して、指定した条件に一致するデータを検索するSQL文である。
SELECT select_list
FROM table_expression
WHERE where_clause [LIMIT {number|ALL}] [OFFSET number]
[ORDER BY sort_expression [ASC|DESC] [NULLS {FIRST|LAST}]]返す結果の最大行数をnumberで指定した行数に制限する。LIMIT ALLを指定した場合は制限しない。
返す結果のオフセットを指定する。
結果を昇順に並び替える。
結果を降順に並び替える。
NULLを先頭にして並び替える。
NULLを末尾にして並び替える。
2学期の試験の点数が100で1ある生徒を検索する例を示す。
SELECT * from exam_score WHERE score[2] = 1001学期から3学期までに試験の点数がひとつでも100である生徒を検索する例を示す。
SELECT * from exam_score WHERE score[1] = 100 OR score[2] = 100 OR socre[3] = 100または次のようなSQLにすることもできる。
SELECT * from exam_score WHERE 100 = ANY (score)ANYをSOMEにしてもよい。
SELECT * from exam_score WHERE 100 = SOME (score)1学期から3学期までの試験の点数がすべて100である生徒を検索する例を示す。
SELECT * from exam_score WHERE score[1] = 100 AND score[2] = 100 AND socre[3] = 100または次のようなSQLにすることもできる。
SELECT * from exam_score WHERE 100 = ALL (score)SETは実行時パラメータを変更するSQL文である。
SET parameter TO valuesearch_pathには、スキーマ名を省略したときに検索するスキーマを指定する。スキーマ名はカンマで区切って複数指定できる。search_pathの設定は、同一セッション内でのみ有効である。
SET search_path TO schemasSHOWは実行時パラメータの値を表示するSQL文である。
SHOW parametersearch_pathは、スキーマ名を省略したときに検索するスキーマである。
SHOW search_pathUPDATEはレコードを更新するSQL文である。
UPDATE [ONLY] table SET column = {expression|DEFAULT} [, ...] [FROM fromlist] [WHERE condition]指定したテーブルのみを更新する。ONLYの指定を省略した場合、指定したテーブルとそのサブテーブルを更新する。
WHERE句やSET句の式に他のテーブル上の列を指定できるようにするテーブル式の集合を指定する。
Oracleデータベースの UPDATE 文とは異なり、PostgreSQL の UPDATE 文では FROM 句を指定できる。これにより、UPDATE 文が簡潔に記述できる。
配列のすべての要素を更新する例を示す。
UPDATE exam_score SET score = '{0,50,100}'または次のようなSQLにすることもできる。
UPDATE exam_score SET score = ARRAY[0,50,100]配列のうちひとつだけ更新することもできる。
UPDATE exam_score SET score[2] = 50配列のうち一部だけ更新することもできる。
UPDATE exam_score SET score[1:2] = '{0,50}'@>は包含するかどうかを返す演算子である。
array @> subarrayにsubが含まれていれば真、含まれていなければ偽と評価される。
ARRAY[1,2,3] @> ARRAY[1,2]<@は包含されるかどうかを返す演算子である。
sub <@ arrayarrayにsubが含まれていれば真、含まれていなければ偽と評価される。
ARRAY[2,3] <@ ARRAY[1,2,3]LIKEは、文字列が指定したパターンと一致するかどうかを返す演算子である。Oracleデータベースの LIKE 演算子と同じ。
expr1 [NOT] LIKE expr2 [ESCAPE character]パターンには特殊な意味を持つ「メタ文字」を含めることができる。
| メタ文字 | 説明 |
|---|---|
_ |
任意の一文字 |
% |
任意の文字の0回以上の繰り返し |
SELECT * FROM emp WHERE ename LIKE 'Horiuchi%'SIMILAR TOは、文字列が指定したパターンと一致するかどうかを返す演算子である。正規表現も一部使用できる。ただし、PostgreSQL においてはピリオド(.)はメタ文字ではない。
expr1 [NOT] SIMILAR TO expr2 [ESCAPE character]パターンには特殊な意味を持つ「メタ文字」を含めることができる。
| メタ文字 | 説明 |
|---|---|
| |
論理和(OR) |
* |
直前の0回以上の繰り返し |
+ |
直前の1回以上の繰り返し |
? |
直前の0回または1回の繰り返し |
{m} |
直前のm回の繰り返し |
{m,} |
直前のm回以上の繰り返し |
{m,n} |
直前のm回以上かつm回以下の繰り返し |
() |
グループ化 |
[...] |
POSIX正規表現と同様な文字クラス |
CURRENT_TIMESTAMP は現在の日付と時刻を取得する関数である。Oracle データベースの SYSDATE に相当する。なお、PostgreSQL に SYSDATE という関数は無い。
NOW は現在の日付と時刻を取得する関数である。ただし、トランザクション内では常に同じ値を返す。トランザクション内で同じ日付と時刻を使いたい場合に便利である。
SELECT NOW()psqlとは、対話的にPostgreSQLデータベースを操作するコマンドである。OracleデータベースにおけるSQL*PlusやMySQLにおけるMySQLモニタに相当する。psqlを起動するとコマンドプロンプトが表示され、SQL文やメタコマンドを実行できる。
psql [options]psql [options] dbnamepsql [options] dbname useroptions には次のオプションを指定できる。
-aまたは-echo-allオプションを指定してpsqlを実行すると、SQLスクリプトからの入力が画面に表示される。
「 psql -U user1 mydb 」は「 psql mydb user1 」と同じ。
テーブルの出力形式を変更する。
データベース内のテーブルを表示する。
テーブルの構造を表示する。
引数を標準出力に出力する。
$ psql db
db=# \echo hello
hello複数の引数を指定した場合は空白で区切られる。
\echo text1 text2最後の引数に -n を指定すると、改行を行わない。
\echo text -n現在のセッションの自動コミット設定を確認する。
exampledb=> \echo :AUTOCOMMIT
on
exampledb=>コマンドの実行結果を標準出力に出力するには、コマンドをバッククオートで囲む。
$ psql db
db=# \echo `pwd`
/usr/tsukaSQLスクリプトファイルを実行する。
以降の問い合わせの結果をファイルに保存する。
以降の問い合わせの結果をUnixシェルに渡してコマンドを実行する。
問い合わせ結果のテーブル出力に影響するオプションを設定する。
psqlを終了する。
psqlで使えるコマンドを表示する。
データベースユーザに関するカタログ。
pg_user はデータベースユーザに関するビューであり、元表の pg_shadow からパスワードに関する情報を取り除いたものである。
PostgreSQLデータベースをクラスタ化する。
PostgreSQL手続き言ををインストールする。
PostgreSQL手続き言語を削除する。
C言語プログラム用の埋め込みSQLプリプロセッサ
PostgreSQLのデータベースクラスタを新たに作成する。
PostgreSQLで使用されるファイル構造を確認する。
PostgreSQL WALアーカイブファイルを消去する。
PostgreSQLクラスタのベースバックアップを取得する。
インストールしたPostgreSQLバージョン情報を表示する。
PostgreSQLデータベースクラスタの制御情報を表示する。
PostgreSQLデータベースクラスタをスクリプトファイルへ抽出する。
PostgreSQLサーバの接続情報を検査する。
PostgreSQLクラスタからトランザクションログをストリームする。
PostgreSQLデータベースの先行書き込みログやその他の制御情報を初期化する。
ウォームスタンバイデータベースサーバを作成する。
PostgreSQLの最も高速なwal_sync_methodを決定する。
時間計測のオーバヘッドを測定する。
PostgreSQLサーバインスタンスを更新する。
PostgreSQLデータベースクラスタの先行書き込みログを表示する。
PostgreSQLを管理するGUIアプリケーション
PostgreSQLに対してベンチマーク試験を行う。
PostgreSQLデータベースのインデックスを再作成する。
PostgreSQLデータベースの不要領域の回収と解析を行う。
PostgreSQLデータベースから不要となったラージオブジェクトを削除する。
データベースを作成する。
createdb [databasename]databasename には、データベース名を指定する。 databasename を省略した場合は、OSのログインユーザ名でデータベースを作成する。
ユーザを作成する。
createuser [options] [username]username にはユーザ名を指定する。 username を省略した場合は、OSのログインユーザ名でユーザを作成する。
既存のデータベースを削除する。
dropdb [options] dbname既存のユーザを削除する。
dropuser [options] [username]バックエンドサーバ(postmaster)を操作する。
pg_ctl subcommand [subcommand-options]pg_ctl start [-w] [-s] [-D datadir] [-l filename] [-o options] [-p path]pg_ctl stop [-W] [-s] [-D datadir] [-m s[mart] | f[ast] | i[mmediate] ]pg_ctl restart [-w] [-s] [-D datadir] [-m s[mart] | f[ast] | i[mmediate] ] [-o options]pg_ctl reload [-s] [-D datadir]pg_ctl status [-D datadir]| コマンド | 説明 |
|---|---|
| start | postmasterを起動する。 |
| stop | postmasterを停止する。 |
| restart | postmasterを再起動する。 |
| reload | postmasterに設定ファイルを再度読み込ませる。 |
| status | postmasterの状態を表示する。 |
pg_ctlclusterは、PostgreSQLクラスタの開始、終了、再開又はリロードを行うコマンドである。pg_ctlclusterコマンドを使用するには、データベースクラスタの所有者又はスーパーユーザである必要がある。
pg_ctlcluster [options] cluster-version cluster-name actionaction には以下のいずれかを指定する。
| アクション |
|---|
| start |
| stop |
| restart |
| reload |
| autovac-start |
| autovac-stop |
| autovac-restart |
$ pg_ctlcluster 9.1 mainpg_lsclustersは、PostgreSQLのクラスタに関する情報を表示するコマンドである。
pg_lsclusters [options]pg_hba.confはクライアントの認証に関する設定ファイルである。
pg_hba.confファイルの例を次に示す。
host studentdb horiuchi 127.0.0.1/32 password
host studentdb horiuchi 127.0.0.1/32 md5環境変数 PGDATA には、データベースのデータを格納するディレクトリを設定する。
export PGDATA=/var/lib/postgresql/9.1/main