PostgreSQLに入門してみる

いまだにRDBをあまり使ったことがない。 恥ずかしい。

ということで、巷で有名なPostgreSQLを使用してみた！

インストール

PostgreSQL: Downloadsからお手持ちのOSに合ったバイナリを落としてきてください。 手順に沿えば勝手にインストールされるはずです。

$ postgres --version
postgres (PostgreSQL) 10.5

postgres --versionコマンドでバージョン情報が表示されればOK

実行！

一覧表示

$ psql -l
                                List of databases
   Name    |  Owner  | Encoding |   Collate   |    Ctype    |  Access privileges
-----------+---------+----------+-------------+-------------+---------------------
 postgres  | kimoton | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | kimoton | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/kimoton         +
           |         |          |             |             | kimoton=CTc/kimoton
 template1 | kimoton | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/kimoton         +
           |         |          |             |             | kimoton=CTc/kimoton
(3 rows)

Postgresで扱うデータは、環境変数PGDATA で指定したディレクトリに保存されるため、あらかじめ指定してあげます。

export PGDATA=/usr/local/var/postgres

試しにtestdbと言う名前で、新規にtestdbという名前のデータベースを作成してみます。

createdb testdb

$ psql -l
                                List of databases
   Name    |  Owner  | Encoding |   Collate   |    Ctype    |  Access privileges
-----------+---------+----------+-------------+-------------+---------------------
 postgres  | kimoton | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | kimoton | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/kimoton         +
           |         |          |             |             | kimoton=CTc/kimoton
 template1 | kimoton | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/kimoton         +
           |         |          |             |             | kimoton=CTc/kimoton
 testdb    | kimoton | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
(4 rows)

作成されました。

このDBに入るには、 psqlに続けてデータベース名を入力します。

$ psql testdb
psql (10.5)
Type "help" for help.

testdb=#

入ってからでも、\lコマンドでデータベースを一覧表示できます。

testdb=# \l
                                List of databases
   Name    |  Owner  | Encoding |   Collate   |    Ctype    |  Access privileges
-----------+---------+----------+-------------+-------------+---------------------
 postgres  | kimoton | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | kimoton | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/kimoton         +
           |         |          |             |             | kimoton=CTc/kimoton
 template1 | kimoton | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/kimoton         +
           |         |          |             |             | kimoton=CTc/kimoton
 testdb    | kimoton | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
(4 rows)

\?を入力すると、\から始まるコマンドを一覧表示します。

testdb=# \?
General
  \copyright             show PostgreSQL usage and distribution terms
  \crosstabview [COLUMNS] execute query and display results in crosstab
  \errverbose            show most recent error message at maximum verbosity
  \g [FILE] or ;         execute query (and send results to file or |pipe)
  \gexec                 execute query, then execute each value in its result
  \gset [PREFIX]         execute query and store results in psql variables
  \gx [FILE]             as \g, but forces expanded output mode
  \q                     quit psql
  \watch [SEC]           execute query every SEC seconds

Help
  \? [commands]          show help on backslash commands
  \? options             show help on psql command-line options
  \? variables           show help on special variables
  \h [NAME]              help on syntax of SQL commands, * for all commands

Query Buffer
  \e [FILE] [LINE]       edit the query buffer (or file) with external editor
  \ef [FUNCNAME [LINE]]  edit function definition with external editor
  \ev [VIEWNAME [LINE]]  edit view definition with external editor
  \p                     show the contents of the query buffer
.
.
.

出るときは\qと入力します。

testdb=# \q

DBの作成

新規にデータベースを作成するには、以下のようなコマンドを打ちます。

create table {テーブル名} (カラム名 要素のデータ型)

ここでは、重要な二つの概念が登場します。 データ型と、制約です。

データ型

データ型については公式ドキュメントを一読しておきましょう。

よく使うデータ型としては、

• プライマリキー：primary key
• シーケンス：serial
• 可変長の文字列：varchar
• 整数：int
• 日時：timestamp

あたりになるかと思います。
データ型を割り当てることで、予期せぬデータが入らないように制限することができます。

制約

カラムに入るデータとして、適切なものかどうかvalidationを行うための制約という機能があります。
これを使えば、不適切なデータを入れようとした際にエラーが起きるように設定することができます。
制約についても公式ドキュメントを参考にすれば良いかと思います。
よく使う制約としては、

• 欠損値でない (not null)
• データの長さがx以上 (check(length({カラム名})) > x))
• 単一の値 (unique)

などがあげられます。

今回は例として、

• IDのカラム
• 3文字以上のvarchar型のnameというカラム、
• 欠損値でないtext型のfeatureというカラム

を持ったgirlfriendsという名前のテーブルを作成します。
こうすると、3文字以上の名前を持ち、特徴を持った（featureカラムがnullでない）彼女しか登録できないことになります。

create table girlfriends (id serial, 
                          name varchar(255),
                          check(length(name) > 3),
                          feature text not null);

作成したテーブルの確認には\dtコマンドを使用します。

testdb=# \dt
        List of relations
 Schema | Name  | Type  |  Owner
--------+-------+-------+---------
 public | girlfriends | table | kimoton
(1 row)

作成したカラムの確認には\dコマンドを使用します。

testdb=# \d girlfriends
                                    Table "public.girlfriends"
 Column  |          Type          | Collation | Nullable |                 Default
---------+------------------------+-----------+----------+-----------------------------------------
 id      | integer                |           | not null | nextval('girlfriends_id_seq'::regclass)
 name    | character varying(255) |           |          |
 feature | text                   |           | not null |
Check constraints:
    "girlfriends_name_check" CHECK (length(name::text) > 3)

データの追加

さぁそれでは彼女を追加していきましょう。 データの追加には、insertコマンドを使用します。

insert into girlfriends {挿入対象のタプル} values {挿入する値のタプル}

適当に追加します。

insert into girlfriends (name, feature) values ('Ai', 'cute and kind');
insert into girlfriends (name, feature) values ('Chisato', 'very cute and kind');
insert into girlfriends (name, feature) values ('Yuha', 'very very cute and kind');

適当です。

上記のように一個ずつデータを追加していっても良いのですが、まとめて追加することもできます。

insert into girlfriends (name, feature) values ('Ai', 'cute and kind'), ('Chisato', 'very cute and kind'), ('Yuha', 'very very cute and kind');

作成した制約に満たないデータを入れようとすると、以下のようにエラーがおきます。

testdb=# insert into girlfriends (name, feature) values ('Ai', 'cute and kind');
ERROR:  new row for relation "girlfriends" violates check constraint "girlfriends_name_check"
DETAIL:  Failing row contains (1, Ai, cute and kind).

girlfriends_name_checkに引っかかったと言われます。

仕方ないのでnameをAi-chanにして挿入しましょう。

testdb=# insert into girlfriends (name, feature) values ('Ai-chan', 'cute and kind');
INSERT 0 1

他のデータについても同様に入れていきます。

table内データの確認

tableに入れたデータの確認にはselectコマンドを使用します。

select * from {table}

ここで、*は「{table}内のfield全て」を指します。

testdb=# select * from girlfriends
;
 id |  name   |         feature
----+---------+-------------------------
  1 | Ai-chan | cute and kind
  2 | Chisato | very cute and kind
  3 | Yuha    | very very cute and kind
(3 rows)

データがちゃんと追加されているのが確認できました。

select文はもちろん表示するカラムの選択もできて、例えばnameのカラムだけ表示したい場合は以下のように実行します。

testdb=# select name from girlfriends
;
  name
---------
 Ai-chan
 Chisato
 Yuha
(3 rows)

テーブル名の変更

テーブル名の変更にはalter tableコマンドを使用します。

alter table {変更前テーブル名} to {変更後テーブル名}

例えば、現状のテーブル名girlfriendsをmygirlfriendsに変更したい場合、以下のように入力します。

testdb=# alter table girlfriends rename to mygirlfriends;
ALTER TABLE

testdb=# \dt
         List of relations
 Schema |  Name   | Type  |  Owner
--------+---------+-------+---------
 public | mygirlfriends | table | kimoton
(1 row)

テーブル名がmygirlfriendsに変更されたことが確認できました。

テーブルの削除

以下のコマンドにより削除します。

drop table {table名}

作成したばかりのmygirlfriendsテーブルを削除するには以下のように入力します。

testdb=# drop table mygirlfriends;
DROP TABLE

testdb=# \dt
Did not find any relations.

まとめ

最後に、紹介したコマンドをまとめておきます。

\から始まるコマンド

コマンド	説明
\?	\から始まるコマンドの一覧表示
\l	DB一覧の表示
\dt	テーブル一覧の表示
\d [テーブル名]	テーブルの項目(フィールド)確認
\q	psqlから抜ける

その他DBを扱うコマンド

コマンド	説明
create table {テーブル名} (カラム名 要素のデータ型)	指定した要素で初期化したテーブルの作成
alter table {変更前テーブル名} to {変更後テーブル名}	テーブル名の変更
drop table {テーブル名}	テーブルの削除