PostgreSQL

PostgreSQLはオープンソースのリレーショナルデータベース管理システム(RDBMS)です。オープンソースのデータベースとしては、MySQLと並んで最もよく使われています。この記事ではPostgreSQLの使い方をご紹介します。

目次

  1. コマンド
    1. psql
      1. \a
      2. \d
      3. \d tablename
      4. \echo text
      5. \echo `command`
      6. \i filename
      7. \o filename
      8. \o command
      9. \pset option
      10. \q
      11. \?
  2. SQL
    1. ALTER FUNCTION
    2. ALTER INDEX
    3. ALTER ROLE
    4. ALTER TABLE
    5. ALTER VIEW
    6. CREATE ROLE
    7. CREATE TABLE
    8. CREATE VIEW
    9. DROP INDEX
    10. DROP ROLE
    11. DROP SEQUENCE
    12. DROP USER
    13. DROP VIEW
    14. GRANT
    15. REVOKE
    16. SELECT
    17. SET
    18. SHOW
    19. UPDATE
    1. CASE
  3. 関数
    1. AVG
    2. CEIL
    3. COALESCE
    4. COUNT
    5. CURRENT_TIMESTAMP
    6. FLOOR
    7. LENGTH
    8. LPAD
    9. MAX
    10. MIN
    11. NOW
    12. REPLACE
    13. ROUND
    14. ROW_NUMBER
    15. RPAD
    16. SUM
    17. TO_CHAR
    18. TO_DATE
    19. TO_NUMBER
    20. TRANSLATE
    21. TRUNC
  4. ビュー
    1. pg_user
  5. カタログ
    1. pg_shadow
  6. 設定ファイル
    1. postgresql.conf
    2. pg_hba.conf
  7. 環境変数
    1. PGDATA
  8. API
    1. JDBC
    2. ODBC

ダウンロード

  1. Webブラウザで日本PostgreSQLユーザ会のWebサイトを開く。
    https://www.postgresql.jp/
  2. ダウンロードページから任意のプラットフォーム用のインストーラをダウンロードする。

インストール

エクスプローラからMicrosoft Windows用PostgreSQLのインストーラを右クリックしてコンテキストメニューを表示させる。メニューから「管理者として実行」をクリックする。

管理者として実行
Figure 1. 管理者として実行

ユーザーアカウント制御のダイアログが表示されて「次のプログラムにこのコンピュータへの変更を許可しますか?」と聞かれるので、プログラム名がPostgreSQLのインストーラであることを確認して、「はい(Y)」ボタンをクリックする。

Windows用PostgreSQLはMicrosoft Visual C++ Redistributableが必要である。まだインストールされていなければ自動的にインストールされる。既にインストールされていれば、Microsoft Visual C++ Redistributableのインストールはスキップされる。

セットアップウィザードが表示されるので、「Next >」ボタンをクリックする。

Setup
Figure 2. Setup

インストールディレクトリを聞かれるので、任意のディレクトリを指定する。基本的にデフォルトのディレクトリでよい。ディレクトリを指定したら「Next >」ボタンをクリックする。

Installation Directory
Figure 3. Installation Directory

インストールするコンポーネントを聞かれるので、インストールするコンポーネントにチェックを入れる。基本的にデフォルトの状態のままでよい。コンポーネントを選択したら「Next >」ボタンをクリックする。

Select Components
Figure 4. Select Components

データベースのデータを保存するディレクトリを聞かれるので、任意のディレクトリを指定する。インストールディレクトリにProgram Files下のフォルダを指定した場合、データディレクトリのデフォルトもProgram Files下のフォルダになるが、ここはユーザのアクセスが制限される場所なので、Program Files下以外に変更した方がよい。

データディレクトリを指定したら「Next >」ボタンをクリックする。

Data Directory
Figure 5. Data Directory

データベース管理者とサービスのアカウント (postgres) のパスワードを聞かれるので、任意のパスワードを入力する。パスワードを入力したら「Next >」ボタンをクリックする。

Password
Figure 6. Password

PostgreSQLサーバが接続を受け付けるポート番号を聞かれるので、任意のポート番号を入力する。基本的にデフォルトの状態のままでよい。ポート番号を入力したら「Next >」ボタンをクリックする。

Port
Figure 7. Port

作成するデータベース・クラスタのロケールを聞かれるので、「C」を選択する。

ロケールを選択したら「Next >」ボタンをクリックする。

Advanced Options
Figure 8. Advanced Options

インストール設定が表示されるので、「Next >」ボタンをクリックする。

Pre Installation Summary
Figure 9. Pre Installation Summary

インストールの準備が整った旨が表示されるので、「Next >」ボタンをクリックする。

Ready to Install
Figure 10. Ready to Install

インストールが実行される。

Installing
Figure 11. Installing

インストールが完了した旨が表示される。PostgreSQLの追加ソフトウェアをインストールするためのツールである「スタックビルダ」を起動するかどうかのチェックがあるので、インストーラを終了させた後に起動させたければチェックを入れる。追加したいソフトウェアが無ければチェックを外してよい。

「Finish」ボタンをクリックすると、PostgreSQLのインストーラが終了する。

Completing
Figure 12. Completing

postgresql

PostgreSQLのサービスを起動

# /etc/init.d/postgresql start

PostgreSQLの起動状態を確認

# /etc/init.d/postgresql status
Running clusters: 9.1/main

PostgreSQLのサービスを停止

# /etc/init.d/postgresql stop

CREATE ROLE

CREATE ROLEはロールを作成するPostgreSQL SQL文である。作成したロールはDROP ROLE文で削除できる。

PostgreSQLでは、いわゆるユーザのことを「ロール」と呼ぶ。Oracleデータベースではユーザとロールは別のものであるが、PostgreSQLではそのような区別はない。

CREATE ROLE name WITH LOGIN PASSWORD 'password'

CREATE TABLE

PostgreSQL では配列が使える。ただし、Oracleでは配列は使えないので、SQLに互換性は無くなる。

生徒ごとに1学期、2学期、3学期の試験の点数を格納するテーブルを作成する例を示す。

CREATE TABLE exam_score (student_no INTEGER, score INTEGER[3])

DROP ROLE

DROP ROLEはCREATE ROLE文で作成したロールを削除するPostgreSQL SQL文である。

PostgreSQLでは、いわゆるユーザのことを「ロール」と呼ぶ。Oracleデータベースではユーザとロールは別のものであるが、PostgreSQLではそのような区別はない。

ロールを削除する。指定したロールが存在しない場合はエラーになる。

DROP ROLE name

ロールを削除する。指定したロールが存在しない場合でもエラーにならない。

DROP ROLE IF EXISTS name

GRANT

GRANTはアクセス権限を付与する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 tsuka

REVOKE

REVOKEは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 user

SELECT

SELECTはデータベースに照会して、指定した条件に一致するデータを検索するSQL文である。

SELECT select_list
  FROM table_expression
  WHERE where_clause [LIMIT {number|ALL}] [OFFSET number]
  [ORDER BY sort_expression [ASC|DESC] [NULLS {FIRST|LAST}]]
LIMIT

返す結果の最大行数をnumberで指定した行数に制限する。LIMIT ALLを指定した場合は制限しない。

OFFSET

返す結果のオフセットを指定する。

ASC

結果を昇順に並び替える。

DESC

結果を降順に並び替える。

NULLS FIRST

NULLを先頭にして並び替える。

NULLS LAST

NULLを末尾にして並び替える。

配列を条件にした検索

2学期の試験の点数が100で1ある生徒を検索する例を示す。

SELECT * from exam_score WHERE score[2] = 100

1学期から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

SETは実行時パラメータを変更するSQL文である。

SET parameter TO value

search_pathには、スキーマ名を省略したときに検索するスキーマを指定する。スキーマ名はカンマで区切って複数指定できる。search_pathの設定は、同一セッション内でのみ有効である。

SET search_path TO schemas

SHOW

SHOWは実行時パラメータの値を表示するSQL文である。

SHOW parameter

search_pathは、スキーマ名を省略したときに検索するスキーマである。

SHOW search_path

UPDATE

UPDATEはレコードを更新するSQL文である。

UPDATE [ONLY] table SET column = {expression|DEFAULT} [, ...] [FROM fromlist] [WHERE condition]
ONLY

指定したテーブルのみを更新する。ONLYの指定を省略した場合、指定したテーブルとそのサブテーブルを更新する。

fromlist

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 @> sub

arraysubが含まれていれば真、含まれていなければ偽と評価される。

ARRAY[1,2,3] @> ARRAY[1,2]

<@

<@は包含されるかどうかを返す演算子である。

sub <@ array

arraysubが含まれていれば真、含まれていなければ偽と評価される。

ARRAY[2,3] <@ ARRAY[1,2,3]

LIKE

LIKEは、文字列が指定したパターンと一致するかどうかを返す演算子である。Oracleデータベースの LIKE 演算子と同じ。

expr1 [NOT] LIKE expr2 [ESCAPE character]
NOT
文字列が指定したパターンに一致しなかった場合にtrueと評価される。
ESCAPE
エスケープ文字を指定する。

パターンには特殊な意味を持つ「メタ文字」を含めることができる。

メタ文字 説明
_ 任意の一文字
% 任意の文字の0回以上の繰り返し
SELECT * FROM emp WHERE ename LIKE 'Horiuchi%'

SIMILAR TO

SIMILAR TOは、文字列が指定したパターンと一致するかどうかを返す演算子である。正規表現も一部使用できる。ただし、PostgreSQL においてはピリオド(.)はメタ文字ではない。

expr1 [NOT] SIMILAR TO expr2 [ESCAPE character]
NOT
文字列が指定したパターンに一致しなかった場合にtrueと評価される。
ESCAPE
エスケープ文字を指定する。

パターンには特殊な意味を持つ「メタ文字」を含めることができる。

メタ文字 説明
| 論理和(OR)
* 直前の0回以上の繰り返し
+ 直前の1回以上の繰り返し
? 直前の0回または1回の繰り返し
{m} 直前のm回の繰り返し
{m,} 直前のm回以上の繰り返し
{m,n} 直前のm回以上かつm回以下の繰り返し
() グループ化
[...] POSIX正規表現と同様な文字クラス

CURRENT_TIMESTAMP

CURRENT_TIMESTAMP は現在の日付と時刻を取得する関数である。Oracle データベースの SYSDATE に相当する。なお、PostgreSQL に SYSDATE という関数は無い。

NOW

NOW は現在の日付と時刻を取得する関数である。ただし、トランザクション内では常に同じ値を返す。トランザクション内で同じ日付と時刻を使いたい場合に便利である。

SELECT NOW()

psql

psqlとは、対話的にPostgreSQLデータベースを操作するコマンドである。OracleデータベースにおけるSQL*PlusやMySQLにおけるMySQLモニタに相当する。psqlを起動するとコマンドプロンプトが表示され、SQL文やメタコマンドを実行できる。

psql [options]
psql [options] dbname
psql [options] dbname user
dbname
データベース名を指定する。
user
ユーザ名を指定する。ユーザ名はpsqlコマンドの「-U」または「--username」オプションで指定することもできる。

options には次のオプションを指定できる。

-a
-echo-all
-fまたは--fileオプションを指定してpsqlを実行すると、SQLスクリプト(SQL文を記述したテキストファイル)を実行できる。また、\iメタコマンドでもSQLスクリプトを実行できる。通常、SQLスクリプトからの入力(ファイルに記載されているSQL文)は画面に表示されない。

-aまたは-echo-allオプションを指定してpsqlを実行すると、SQLスクリプトからの入力が画面に表示される。

-d dbname
データベース名を指定する。
-f filename
--file=filename
対話的にコマンドを実行するのではなく、filenameで指定したSQLスクリプトファイル(SQLコマンドを記述したテキストファイル)を実行して、psqlを終了する。
-U username
--username=username
デフォルトのユーザではなく username ユーザとしてデータベースに接続する。

「 psql -U user1 mydb 」は「 psql mydb user1 」と同じ。

\a

テーブルの出力形式を変更する。

\d

データベース内のテーブルを表示する。

\d tablename

テーブルの構造を表示する。

\echo

引数を標準出力に出力する。

$ psql db
db=# \echo hello
hello

複数の引数を指定した場合は空白で区切られる。

\echo text1 text2

最後の引数に -n を指定すると、改行を行わない。

\echo text -n

現在のセッションの自動コミット設定を確認する。

exampledb=> \echo :AUTOCOMMIT
on
exampledb=>

\echo `command`

コマンドの実行結果を標準出力に出力するには、コマンドをバッククオートで囲む。

$ psql db
db=# \echo `pwd`
/usr/tsuka

\i filename

SQLスクリプトファイルを実行する。

\o filename

以降の問い合わせの結果をファイルに保存する。

\o command

以降の問い合わせの結果をUnixシェルに渡してコマンドを実行する。

\pset option

問い合わせ結果のテーブル出力に影響するオプションを設定する。

\q

psqlを終了する。

\?

psqlで使えるコマンドを表示する。

pg_shadow

データベースユーザに関するカタログ。

pg_user

pg_user はデータベースユーザに関するビューであり、元表の pg_shadow からパスワードに関する情報を取り除いたものである。

clusterdb

PostgreSQLデータベースをクラスタ化する。

createlang

PostgreSQL手続き言ををインストールする。

droplang

PostgreSQL手続き言語を削除する。

ecpg

C言語プログラム用の埋め込みSQLプリプロセッサ

initdb

PostgreSQLのデータベースクラスタを新たに作成する。

oid2name

PostgreSQLで使用されるファイル構造を確認する。

pg_archivecleanup

PostgreSQL WALアーカイブファイルを消去する。

pg_basebackup

PostgreSQLクラスタのベースバックアップを取得する。

pg_config

インストールしたPostgreSQLバージョン情報を表示する。

pg_controldata

PostgreSQLデータベースクラスタの制御情報を表示する。

pg_dumpall

PostgreSQLデータベースクラスタをスクリプトファイルへ抽出する。

pg_isready

PostgreSQLサーバの接続情報を検査する。

pg_receivexlog

PostgreSQLクラスタからトランザクションログをストリームする。

pg_resetxlog

PostgreSQLデータベースの先行書き込みログやその他の制御情報を初期化する。

pg_standby

ウォームスタンバイデータベースサーバを作成する。

pg_test_fsync

PostgreSQLの最も高速なwal_sync_methodを決定する。

pg_test_timing

時間計測のオーバヘッドを測定する。

pg_upgrade

PostgreSQLサーバインスタンスを更新する。

pg_xlogdump

PostgreSQLデータベースクラスタの先行書き込みログを表示する。

pgAdmin3

PostgreSQLを管理するGUIアプリケーション

pgbench

PostgreSQLに対してベンチマーク試験を行う。

reindexdb

PostgreSQLデータベースのインデックスを再作成する。

vacuumdb

PostgreSQLデータベースの不要領域の回収と解析を行う。

vacuumlo

PostgreSQLデータベースから不要となったラージオブジェクトを削除する。

createdb

データベースを作成する。

createdb [databasename]

databasename には、データベース名を指定する。 databasename を省略した場合は、OSのログインユーザ名でデータベースを作成する。

createuser

ユーザを作成する。

createuser [options] [username]

username にはユーザ名を指定する。 username を省略した場合は、OSのログインユーザ名でユーザを作成する。

-d
--createdb
作成するユーザにデータベース作成を許可する。
-P
--pwprompt
作成するユーザのパスワードを入力するプロンプトを表示する。

dropdb

既存のデータベースを削除する。

dropdb [options] dbname
-h host
--host host
ホスト名
-p port
--port port
ポート番号
-U username
--username username
接続に使用するユーザ名

dropuser

既存のユーザを削除する。

dropuser [options] [username]
-h host
--host host
ホスト名

pg_ctl

バックエンドサーバ(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]
pg_ctlのサブコマンド
コマンド 説明
start postmasterを起動する。
stop postmasterを停止する。
restart postmasterを再起動する。
reload postmasterに設定ファイルを再度読み込ませる。
status postmasterの状態を表示する。
-D datadir
データベースファイルのディレクトリを指定する。省略した場合、環境変数 PGDATA が使われる。
-m mode
停止モード
-w
起動又は停止処理が終了するのを待つ。
-W
起動又は停止処理が終了するのを待たない。

pg_ctlcluster

pg_ctlclusterは、PostgreSQLクラスタの開始、終了、再開又はリロードを行うコマンドである。pg_ctlclusterコマンドを使用するには、データベースクラスタの所有者又はスーパーユーザである必要がある。

pg_ctlcluster [options] cluster-version	cluster-name action

action には以下のいずれかを指定する。

アクション
start
stop
restart
reload
autovac-start
autovac-stop
autovac-restart
-o option
option で指定したオプションをpostmasterのコマンド行オプションとしてそのまま渡す。
$ pg_ctlcluster 9.1 main

pg_lsclusters

pg_lsclustersは、PostgreSQLのクラスタに関する情報を表示するコマンドである。

pg_lsclusters [options]

pg_hba.conf

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

環境変数 PGDATA には、データベースのデータを格納するディレクトリを設定する。

export PGDATA=/var/lib/postgresql/9.1/main