理工系研究者のための Mac OS X, Windows, Linux メモ
 
ホーム >> ソフトウェア >> PostgreSQL

PostgreSQL

更新日：2010/11/21
更新日：2011/7/20

はじめに

PostgreSQLはオープンソースのデータベース。 SQL言語にも対応しています。 国際化にも対応しており日本語も使えます。 Linux等UNIX系のOSだけでなくWindowsやMac OS Xにも対応しています。

ここで紹介するインストール方法や使い方はMac OS X上で確認したものですが、他のUNIX系OS（Linux, FreeBSD等）でも共通なので参考になると思います。

PostgreSQLのインストール

インストール方法はこちらのページをご覧ください。

PostreSQLデータベースの起動

データベースを起動するにはコマンドpg_ctlを用います。 ユーザーpostgresになってから実行します。

$ pg_ctl -D /usr/local/pgsql/data start

環境変数PGDATAを指定している場合は -D のオプションを点けなくてもかまいません。停止する時には、stopオプションを指定します。 

$ pg_ctl -D /usr/local/pgsql/data stop

その他、restart（再起動）, status（状態確認）も使えます。

サーバーの設定ファイルは /usr/local/pgsql/data/postgresql.conf にあるので必要なら編集します。

ユーザー登録

PostgreSQLのデータベースをpostgresユーザー以外の人が使うためには、ユーザー登録する必要があります。 例えばMac OS Xのシステム上のユーザーfooが、データベースを作成したり、テーブルを編集したり、データを取得したりしたい場合にはPostgreSQLにユーザーfooを登録します。

ユーザーの登録にはコマンドcreateuserを用います。

$ createuser foo

このコマンドを実行すると、登録するユーザーにデータベースの作成権限を与えるかどうか、登録するユーザーに新しいユーザーを登録する権限を与えるかどうか問われるので、必要に応じてy(yes)かn(no)を指定します。

登録したユーザーを削除するにはコマンドdropuserを用います。 

$ dropuser foo

データベースの作成，削除

データベースを作成するにはcreatedbコマンドを使います。

作成

$ createdb データベース名

データベースの文字コードをデフォルトの文字コードと異なるものに指定する場合には，オプション-Eを用いて， 

$ createdb -E UTF-8 データベース名
とします。

削除

$ dropdb データベース名

データベースの確認

作成されているデータベースを確認するには、オプションlを付けてpsqlを実行します。 

$ psql -l

データベース操作 

$ psql -d データベース名

オプション-dは付けなくてもかまいません。データベースを起動すると、SQLのコマンド入力待ち状態になります。SQLコマンドを用いて、データベースの編集ができます。SQLコマンド以外に円マーク（又はバックスラッシュ）付きのpsql用のコマンドがあるので、よく使うものを次に紹介します。

円マークコマンド

「\」という文字が円マーク（￥）に見えるか、バックスラッシュ（＼）に見えるかは環境やフォントに依るので、ここでは同じものとします。

¥d テーブル一覧
¥d <テーブル名> テーブルの定義を表示
¥l データベース一覧
¥q 終了する
¥! <UNIXコマンド> UNIXコマンド実行
¥encoding <文字コード> 文字コード設定

テーブルの作成，削除(CREATE TABLE, DROP TABLE)

作成

CREATE TABLE テーブル名 (
カラム名 データ型,
カラム名 データ型,
... .....);

テーブルを作成する時、カラム名に大文字が入っている場合でも小文字に変換して登録されます。つまり 

CREATE TABLE mytable ( Name TEXT, ADRESS TEXT);
のように記述しても、カラム名はそれぞれname, addressとして作成されます。大文字で登録したい場合にはダブルクォーテーションで囲まなければならなりません。 
CREATE TABLE mytable ( "Name" TEXT, "ADRESS" TEXT);
このように大文字入りで登録した場合には、SELECTコマンドなどを用いる時にもカラム名を全てダブルクォーテーションで囲む必要があります。混乱を避けるためにもカラム名がASCII文字のみからなる場合は小文字で登録した方が良いかもしれません。

日本語のカラム名はダブルクォーテーションで囲んでもよいし、そのままでも大丈夫です。

削除

DROP TABLE テーブル名;

行の追加(INSERT)

テーブル「住所録」に新しい行を追加するには次のように、必要なデータを順に並べて指定します。

INSERT INTO 住所録 VALUES ('山田太郎', '319-0001', '茨城県', '0294-12-3456');

または、列名を指定して、その値だけ入力することも出来ます。

INSERT INTO 住所録 (名前、住所) VALUES ('山田太郎', '茨城県');

カラム名に大文字が含まれる場合には、カラム名をダブルクォーテーションで囲む必要があります。 

INSERT INTO myaddressbook ("Name", "Address") VALUES ('山田太郎', '茨城県');

データの修正(UPDATE)

テーブル「住所録」の内容のうち、氏名「山田太郎」の郵便番号と電話番号を修正するには次のようにします。カラム名に大文字が含まれる時には、カラム名をダブルクォーテーションで囲む必要があります。

UPDATE 住所録 SET 郵便番号="319-0012", 電話番号="0294-98-7654" WHERE 氏名="山田太郎";

行の削除(DELETE)

テーブル「住所録」から、氏名「山田太郎」のデータを削除するには次のようにします。

DELETE FROM 住所録 WHERE 氏名="山田太郎";

WHEREで指定しないと、全てのデータが削除されるので注意。

新しい列の追加(ADD COLUMN)

テーブル「住所録」にデータ型が text のカラム「電話番号」を追加する。

ALTER TABLE 住所録 ADD COLUMN 電話番号 text;

列の削除(DROP COLUMN)

テーブル「住所録」からカラム「電話番号」を削除する。

ALTER TABLE 住所録 DROP COLUMN 電話番号;

列の名前変更(RENAME COLUMN)

テーブル「住所録」のカラム「電話番号」を「固定電話番号」に変更する。 

ALTER TABLE 住所録 RENAME COLUMN 電話番号 TO 固定電話番号;

テーブル名を変更(RENAME)

テーブル名「住所録」を「職場住所録」に変更する。

ALTER TABLE 住所録 RENAME TO 職場住所録;

データ行にシリアルナンバーを付ける 

CREATE TABLE paperdb (
paper_id serial primary key,
author text,
.....
);

のように serial primary key の id を作ります。

この時、同時にpaperdb_paper_id_seq という sequence が作成されます。他のユーザーがこのテーブルを使う時には paperdb だけでなく paperdb_paper_id_seq にも GRANTでアクセス権限を与える必要があります。

データの表示（SELECT)

条件で選択(WHERE)

SELECT * FROM <テーブル> WHERE <条件>;

条件にはカラム名と値の関係を示します。例えば

SELECT * FROM mytable WHERE name='山田太郎' AND age>25;

のように書くことができます。またLIKEを用いて、部分一致検索を行うこともできます。

SELECT * FROM mytable WHERE address LIKE '東京都%';

%を付けることで、前方一致検索を行います。'%一丁目%'とすれば、addressに「一丁目」が含まれるデータの検索が出来ます。

行の数を数える(count)

SELECT count(*) FROM <テーブル>;

合計を計算する(sum)

SELECT sum(数量) FROM <テーブル>;

平均を計算する(avg)

SELECT avg(数量) FROM <テーブル>;

最大値、最小値を計算する(max, min)

SELECT min(数量), max(数量) FROM <テーブル>;

行を並び替える(ORDER BY)

SELECT * FROM <テーブル> WHERE <条件> ORDER BY 数量 [DESC];

※数量の多い順に並び替えるなら「数量」の後に DESC を付けます。数量の少ない順に並び替えるときはASCを付けます。

アクセス権の変更(GRANT, REVOKE)

初期の状態では、データベースにアクセスできるのは initdb を行ったPostgreSQL のスーパーユーザー（postgres）だけです。その後、createuserを用いてUNIXユーザー名と同じ名前でPostgreSQLユーザーを作成し、アクセス権減を与えると、他のUNIXユーザーもデータベースへアクセスできるようになります。

他のユーザーが作ったデータベース中のテーブルにアクセスできるようにするためには GRANT コマンドを用いてテーブル単位でアクセス許可を与えます。

例えばユーザー postgres が作成したデータベース mydb の中のテーブル addresslist に対して、別のユーザー apache が検索できるようにするためには、 

postgres=# GRANT SELECT ON addresslist TO apache;

とします。逆に、権限を取り上げるには、

postgres=# REVOKE SELECT ON addresslist FROM apahe;

とします。アクセス権を確認するには \z を用ます。

バックアップとリストア

例えばmydb というラージオブジェクトを含むデータベースを tar ファイルにダンプするには、

$ pg_dump -Ft -o mydb > db.tar

オプション-Fは，書き出すファイルのフォーマットで次の3つが選択できます。

オプション説明
-Fccustom：バイナリ形式の単一ファイル
-Fttar:バイナリ形式の複数ファイルをtarで固めたもの
-Fpplant text:SQLスクリプトからなるテキストファイル

データベースにラージオブジェクトが含まれる場合にはplain text以外のバイナリ形式でダンプしなければなりません。

オプション -o はOID（オブジェクトID）を用いている場合に付けます。バージョン8.1以前のときには、ラージオブジェクトをバックアップするために -b を付ける必要がありましたが、付けなくてもバックアップされるようになりました。

このデータベース(ラージオブジェクトを含むもの)を既存の newdb というデータベースにリロードするには、 

$ pg_restore -d newdb -Ft db.tar
とします。

plain textで書き出した場合（書き出したファイル名をdb.sqlとする）はpg_restoreではなくて， 

$ psql -f db.sql newdb
とする必要があります（次節を参照）。

データベースの作成からユーザーの登録まですべてのデータベースをまとめてバックアップしたい場合にはコマンド pg_dudmpall を用います。

▼参考サイト or 参考文献

スクリプトファイル

コマンドラインをあらかじめファイルとして準備しておき、それを読み込ませることができます。例えばターミナルが日本語入力に対応していない場合は、日本語入力の出来るテキストエディタでスクリプトを作成し、読み込ませることで日本語データの入力が出来ます。データベースの文字コードをEUC-JPとしているので、スクリプトファイルに日本語が含まれる場合には、EUC-JPとしなければなりません。

ターミナルからスクリプトファイルを読み込ませるにはオプションfを使って、

$ psql -f ＜スクリプトファイル名＞ ＜データベース名＞

とします。

psql のコマンドラインの場合は、

# \i ＜スクリプトファイル名＞

とします。

参考サイト・参考文献