PostgreSQL の基礎

PostgreSQL の使い方

PostgreSQL は、オブジェクト指向の RDBMS です。奥が深いので、すべてを理解するのは非常に困難ですので（私も良く分かってないし）、簡単な部分から説明していきたいと思います。私の説明では、分からない部分、もっと深く知りたい部分などは、付属のドキュメントや、オンラインマニュアルなどを参考にしてください。

ユーザの作成

インストールの説明と重複しますが、まず、PostgreSQL を使用するユーザを作成しなければなりません。作成と言っても、UNIX に新たなユーザを作るわけではありません（まあ、必要があれば作ってください）。インストールした直後のPostgreSQL には、PostgreSQL のスーパーユーザ以外は使用できないようになっています。したがって、UNIX システムに登録されているユーザが、PostgreSQL を使えるように、PostgreSQL 内にユーザ登録をしなければならないのです。

まず、PostgreSQL のスーパーユーザでログインして下さい。ユーザを登録するには、createuser と言うコマンドを使います。このコマンドを起動すると、どのユーザを登録するか聞いてきます。ここに、UNIX システムのユーザ名を入力します。たとえば、普段使用している自分のユーザ名が「jibun」というユーザ名だったら、ここに「jibun」と入力します。次に、この登録するユーザが、データベースを作ることができるかを聞いてきます。普通は、作れる必要があるので、「y」と入力し、リターンキーを押します。最後に、このユーザが、新しいユーザを作れる（PostgreSQL に登録できる）かを聞いてきますので、好きな方を選んでリターンキーを押してください（y を選ぶと PostgreSQL のスーパーユーザと同じ権限になるので、なるべく n を選択した方がよいでしょう）。これで、作成したユーザが、PostgreSQL に接続できるようになります。

> createuser
Enter name of user to add: jibun
Shall the new user be allowed to create databases? (y/n) y
Shall the new user be allowed to create more new users? (y/n) n
CREATE USER

データベースの作成

まず、自分のデータベースをつくらなければなりません。それには、createdb と言うコマンドを使います。単に、createdb と入力すると、自分のユーザ名と同じ名前のデータベースができます。もし、他の名前にしたければ、引数で、作る名前を与えなければなりません。また、データベースで使用する文字コードも指定することができます。ここでは、日本語 EUC コードでデータベースを作成します。なお、ユーザの作成で、データベースを作れるかどうかに、「y」と指定したユーザでしか、データベースをつくれないのに注意してください。

> createdb -E EUC_JP wwwdb
CREATE DATABASE

と入力すれば、「wwwdb」と言う名で、データベース内部で日本語を EUC で管理するデータベースが作成されます。EUC で管理しているからと言って、Shift_JIS などが使えないわけではなく、Shift_JIS で入出力したい場合は、後述のクライアントの自動変換機能が利用できるので、心配しないでください。もし中国語や、韓国語など複数の言語を同時にサポートしたい場合は、「UNICODE」や、「MULE_INTERNAL」を指定すると良いでしょう。

PostgreSQL には、最初から template0, template1 という二つのデータベースが存在しますが、このデータベースはいじってはいけません。その名の通り、新しいデータベースを作ったときのテンプレートとなるデータベースです。ユーザが利用するデータベースは、上記の作業通り新しく作る必要があります。

psql の使い方

PostgreSQL は、他のデータベースの例にもれず、SQL 文を解釈します。SQL と言う言葉を初めて聞く人もいるでしょうから、簡単に説明します。SQL とは、単純に、言語です。C 言語や、Perl などと同じで、一連の命令を持っています。その手の本を読めば、いろいろ書いてありますが、データベースにデータを登録するには、「insert」と言う命令を使いますし、登録されているデータを取り出すには、「select」と言う命令を使います。PostgreSQL には、この SQL 文をデータベースに伝えるためのツールを用意してあります。それが、psql と言うユーティリティです。

コマンドラインから、psql と入力してください。もし、データベースをつくる段階で、引数を渡して違う名前のデータベースを作ったのなら、同じように、psql にその名前を与えてください。上の例のように、「wwwdb」と言う名のデータベースに接続したい場合は、

> psql wwwdb

とすれば、wwwdb に接続されます。うまく接続できたでしょうか。「データベース名=> 」というのが出たら成功です（スーパーユーザの場合は データベース名=#）。データベース名のところには、接続したデータベースの名前が入ります。上の例では、「wwwdb=>」となります。これが、SQL 文を打つためのコマンドプロンプトとなります。SQL 文以外でも、psql 自体に命令を与えることができます。先頭がバックスラッシュ「\」の文は、SQL 文ではなく、psql に対する命令だと解釈されます。たとえば、「\q」は psql を終了させます。また、「\?」は、psql が理解する命令の一覧を表示してくれます。

もし、自分が、文字コードとして Shift_JIS コードを使っているのなら、次のコマンドを打つことで、Shift_JIS の入出力が可能となります。これにより、ユーザからの入力、画面への表示が Shift_JIS となります。ただし、実際のデータベースには、createdb を実行した時に指定した、文字コードに変換されて格納されます。

=> \encoding SJIS

次に、テーブルを作り、データの追加、変更、削除等の説明を行いますが、他のデータベース等を利用したことがあり、SQL 文を知っている方は、psql の便利なコマンドに進んでください。PostgreSQL 固有の説明はほとんどしていないからです。

テーブルの作成（create table）

まず、データを格納するためのテーブルを作成しなければ行けません。PostgreSQL では、テーブルのことを、クラスと言う場合があります。ここらへんは、オブジェクト指向をうたっているので、当然かも知れません。と、いうのは、「継承」が行えるからです。まあ、そうは言っても、ここでは、テーブルを新規に作成します。簡単なテーブルがいいので、名前と、歳と、住所の３つのカラムを持ったテーブルを作ります。テーブルの作成には create table 文を使います。

=> create table meibo (
->     name     varchar(30) not null,
->     age      integer,
->     address  varchar(80));
CREATE

この意味の解説の前に、SQL 文について少し。普通一つの SQL 文は、必ず最後はセミコロン「;」で終了します。その間改行しても構いません。スペースの入れられるところならどこでも改行することができます（セミコロンを入力するまで、psql のコマンドプロンプトの表示が変わります）。

テーブルを作るときは、こんな感じの SQL 文を発行します。テーブル名、カラム名にも日本語が使えるので、わざわざ、英語にする必要はないかも知れません（どちらがいいかは宗教的な問題です(^_^;）。この SQL 文は 「create table テーブル名 (カラム);」と、指定します。カラムの部分には、カラム名と、属性（型）の組合わせを、カンマ「,」で区切って、１つ以上指定することができます。上の例では、name、age、address の３つのカラムを、指定しています。

それぞれの属性は、30 バイトの varchar と、4 バイトの数値型、80 バイトの varchar です。integer は 4 バイトで表せる符号付き整数（-2147483648 から +2147483647）で、varchar と言うのは、指定されたバイト数以下の、可変長の文字列です。似たような文字型に char 型がありますが、これは指定バイトより、格納するデータの文字が少ない場合は、後ろにスペースが付けられます。

「not null」と言うのは、「null」を許さない、と言う意味です。null と言うのは、C 言語などで言う null 文字「\0」のことではなく、「データが格納されていない状態」のことです。これは、0 バイトの文字のこととは違います。0 バイトの文字は、長さが 0 の文字と言うデータが格納されるのです。もうちょっと説明が進めば分かると思うのですが・・・。

テーブル名、カラム名に、大文字を使っていますが、内部的には大文字／小文字の区別はありません。見易さのために大文字にしているだけです。これを、psql から実行すると、成功すれば CREATE と表示されます。テーブルを作るときは、こんな感じの SQL 文を発行します。

データの追加

新規に作ったテーブルには、当然データが存在しません。したがって、このテーブルにデータを追加する必要があります。追加には、SQLの「insert into」文を使う必要があります。

=> insert into meibo values (
->     'Atsuzaki Hizuya',
->     23,
->     'Tokyo dokoka 1-2-3');
INSERT 16564 1

データを入れるときは、こんな感じの SQL 文を発行します。「insert into テーブル名 values (カラムに入れるデータ);」と、します。カラムに入れるデータは、この書式の場合は、カラムに対応する値をすべて指定する必要があります。つまり、name に対応するのが 'Atsuzaki Hizuya'、age に対応するのが 23、address に対応するのが 'Tokyo dokoka 1-2-3' です。varchar の様な、文字列型の場合は、シングルクォーテーションで囲む必要がありますが、数値型の場合は囲みません。

これを、psql から実行すると、成功すれば INSERT 137968 1 という感じで表示されます。真ん中の数値の部分は、環境によって違ってきます。始めのうちは、この値はたいして気にしないでください。こうして、データの値の部分を変えることによって、どんどんデータを追加することができます。いろいろなデータを追加してみてください。例えば、カラムに指定したデータ長より長いデータを入れてみるとか。

データを表示する

テーブルにデータを追加したら、表示してみたくなります。当然です。それには SQL の「select」文を使います。

=> select * from meibo;
      name       | age |      address
-----------------+-----+--------------------
 Atsuzaki Hizuya |  23 | Tokyo dokoka 1-2-3
(1 row)

上の例のように、「select カラム名 from テーブル名;」と指定します。アスタリスク「*」は、指定されたテーブルの、すべてのカラムを対象とします。ここに、直接カラム名を指定することができます。

=> select name from meibo;
      name
-----------------
 Atsuzaki Hizuya
(1 row)

=> select name, age from meibo;
      name       | age
-----------------+-----
 Atsuzaki Hizuya |  23
(1 row)

上側の例では、name カラムだけを表示します。下側の例は、name と、age のカラムを表示します。このように、複数指定する場合は、カンマ「,」で区切ればいいのです。

さて、データ量が少ないうちはこれでいいのですが、データが増えた場合どうすればいいのでしょうか。数百件あるデータから目的のものを探すのは大変です。目で探していたら、せっかくのデータベースの意味がありません。そこで、select 文に、「where 句」と言うのをつけます。例えば、age が 31 のデータだけを表示したい場合の例をあげます。

=> insert into meibo values (
->     'Ryouma Sakamoto',
->     31,
->     'Kouchi Tosa');
INSERT 16565 1
=> select * from meibo
->     where age = 31;
      name       | age |   address
-----------------+-----+-------------
 Ryouma Sakamoto |  31 | Kouchi Tosa
(1 row)

つまり、where の後に、条件式が書けるのです。name が、A で始まっているデータだけを表示したい場合は、次のようになります。

=> select * from meibo
->     where name like 'A%';

これは、前方一致検索をしているのですが、この場合は like を使い、ワイルドカードの文字として、パーセント「%」を使うのです。もちろん、これらは、and や、or を使い、複数の条件を設定することも可能です。name に a を含み、age が 23 のデータを表示したい場合は、次のようになります。

=> select * from meibo
->     where name like '%a%'
->       and age = 23;
      name       | age |      address
-----------------+-----+--------------------
 Atsuzaki Hizuya |  23 | Tokyo dokoka 1-2-3
(1 row)

データを変更する

すでに登録されているデータを変更したくなることがあります。たとえば、name が 'Atsuzaki Hizuya' さんの住所が変わったとしましょう。次の文で、変更できます。

=> update meibo set
->         address = 'Osaka dokoka 9-8-7'
->     where name = 'Atsuzaki Hizuya';
UPDATE 1
=> select * from meibo;
      name       | age |      address
-----------------+-----+--------------------
 Ryouma Sakamoto |  31 | Kouchi Tosa
 Atsuzaki Hizuya |  23 | Osaka dokoka 9-8-7
(2 rows)

変更する場合は、「変更するカラム = 新しい値」と指定します。そして、必ず変更するデータを、where 句で指定する必要があります。もし、where 句に何も指定しないと、全てのデータが変更されます。

次のように、複数のカラムを、一度に変更することもできます。複数のカラムを指定するときは、間をカンマ「,」で区切ります。

=> update meibo set
->         age     = 24 ,
->         address = 'Nagoya 5-5-5'
->     where name = 'Atsuzaki Hizuya';
UPDATE 1
=> select * from meibo;
      name       | age |   address
-----------------+-----+--------------
 Ryouma Sakamoto |  31 | Kouchi Tosa
 Atsuzaki Hizuya |  24 | Nagoya 5-5-5
(2 rows)

データを削除する

登録されているデータを削除することもできます。

=> delete from meibo
->     where name = 'Atsuzaki Hizuya';
DELETE 1
=> select * from meibo;
      name       | age |   address
-----------------+-----+-------------
 Ryouma Sakamoto |  31 | Kouchi Tosa
(1 row)

必ず削除するデータを、where 句で指定する必要があります。もし、where 句に何も指定しないと、全てのデータが削除されます。気をつけましょう。

テーブルの削除（drop table）

時として、テーブルごと削除したくなるときがあります。カラム名を間違えてしまったときとか、カラムが足らなかったときとか。もちろん削除せずに、カラムの追加等を行うことも出来ますが、結構、削除してからテーブルをつくり直すことも多いのです。

=> drop table meibo;
DROP

これで、meibo テーブルが削除されます。なお、当然のことながら、テーブル内にあるデータも削除されます。これは、取り返しの付かない操作ですので、気をつけて行ってください。

psql の便利なコマンド

psql で使える、便利なコマンドをあげます。