こんにちは、さるまりんです。
これまでここでも、PostgreSQL を使うときはクライアントコマンドの psql をよく利用してきました。
ただ、psql 自体の使い方 については、あまり触れてこなかった気がします。
GUI のクライアントも便利なのですが、
サーバーに SSH で入った先でサッと確認したいとき や、
Docker で立てた検証環境を軽く触りたいとき には psql を扱えたら便利です。
そこで今回は、psql だけを使って
クラシック音楽のミニデータベース を作ってみます。
一連の流れで psql の使い方を復習できればと思います。
1. DockerでPostgreSQLを立ち上げる
まずは検証用に、Docker で PostgreSQL コンテナを立ち上げます。
docker run --name pg-psql-demo \
-e POSTGRES_PASSWORD=postgres \
-e POSTGRES_USER=postgres \
-e POSTGRES_DB=postgres \
-p 5432:5432 \
-d postgres:16起動できたら、ローカルにインストールした psql から接続します。
psql -h localhost -U postgres -d postgres実行すると、こんな感じで psql のプロンプトに入れます。
Password for user postgres:
psql (18.1, server 16.9 (Debian 16.9-1.pgdg120+1))
Type "help" for help.
postgres=# 2. psqlのメタコマンドで「今どこにいるか」を確認する
psql には、SQL とは別に メタコマンド が用意されています。
\ から始まるコマンドです。
まずは、データベース一覧と接続情報を見てみます。
postgres=# \l
List of databases
Name | Owner | Encoding | Locale Provider | Collate | Ctype | Locale | ICU Rules | Access privileges
-----------+----------+----------+-----------------+------------+------------+--------+-----------+-----------------------
postgres | postgres | UTF8 | libc | en_US.utf8 | en_US.utf8 | | |
template0 | postgres | UTF8 | libc | en_US.utf8 | en_US.utf8 | | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | libc | en_US.utf8 | en_US.utf8 | | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
(3 rows)\conninfo で、今どこに接続しているかを確認します。
postgres=# \conninfo
Connection Information
Parameter | Value
----------------------+-----------
Database | postgres
Client User | postgres
Host | localhost
Host Address | ::1
Server Port | 5432
Options |
Protocol Version | 3.0
Password Used | true
GSSAPI Authenticated | false
Backend PID | 77
SSL Connection | false
Superuser | on
Hot Standby | off
(13 rows)テーブル一覧（まだ何もない状態）も見ておきます。
postgres=# \dt
Did not find any tables.3. クラシック音楽用のデータベースを作る
次に、クラシック音楽用のデータベースを 1 つ作ります。
postgres=# CREATE DATABASE classical_demo;
CREATE DATABASE
postgres=# \c classical_demo
psql (18.1, server 16.9 (Debian 16.9-1.pgdg120+1))
You are now connected to database "classical_demo" as user "postgres".
classical_demo=# psql では、\c でデータベースを切り替えられるのが便利ですね。
4. 作曲家テーブル & 作品テーブルを作る
クラシック音楽らしく、
- 作曲家テーブル
composers - 作品テーブル
works
の 2 つを作ります。
CREATE TABLE composers (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
born INT,
died INT,
nationality TEXT
);
CREATE TABLE works (
id SERIAL PRIMARY KEY,
composer_id INT REFERENCES composers(id),
title TEXT NOT NULL,
genre TEXT,
year INT,
duration_min INT
);psql 上ではこんなふうに見えます。
classical_demo=# \dt
List of tables
Schema | Name | Type | Owner
--------+-----------+-------+----------
public | composers | table | postgres
public | works | table | postgres
(2 rows)`\d` でテーブル定義の詳細も確認できます。
classical_demo=# \d composers
Table "public.composers"
Column | Type | Collation | Nullable | Default
-------------+---------+-----------+----------+---------------------------------------
id | integer | | not null | nextval('composers_id_seq'::regclass)
name | text | | not null |
born | integer | | |
died | integer | | |
nationality | text | | |
Indexes:
"composers_pkey" PRIMARY KEY, btree (id)
Referenced by:
TABLE "works" CONSTRAINT "works_composer_id_fkey" FOREIGN KEY (composer_id) REFERENCES composers(id)classical_demo=# \d works
Table "public.works"
Column | Type | Collation | Nullable | Default
--------------+---------+-----------+----------+-----------------------------------
id | integer | | not null | nextval('works_id_seq'::regclass)
composer_id | integer | | |
title | text | | not null |
genre | text | | |
year | integer | | |
duration_min | integer | | |
Indexes:
"works_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
"works_composer_id_fkey" FOREIGN KEY (composer_id) REFERENCES composers(id)DDL を書いたあとにすぐ \d で確認できる psql は気持ちがいいですね〜。
5. データをINSERTしてクラシック音楽DBっぽくする
まずは作曲家を 3 人だけ入れてみます。
classical_demo=# INSERT INTO composers (name, born, died, nationality)
VALUES
('Ludwig van Beethoven', 1770, 1827, 'German'),
('Wolfgang Amadeus Mozart', 1756, 1791, 'Austrian'),
('Frédéric Chopin', 1810, 1849, 'Polish');
INSERT 0 3
classical_demo=# SELECT * FROM composers;
id | name | born | died | nationality
----+-------------------------+------+------+-------------
1 | Ludwig van Beethoven | 1770 | 1827 | German
2 | Wolfgang Amadeus Mozart | 1756 | 1791 | Austrian
3 | Frédéric Chopin | 1810 | 1849 | Polish
(3 rows)続いて、代表的な作品をいくつか登録します。
classical_demo=# INSERT INTO works (composer_id, title, genre, year, duration_min)
VALUES
(1, 'Symphony No.5 in C minor, Op.67', 'Symphony', 1808, 35),
(1, 'Piano Sonata No.14 "Moonlight"', 'Piano Sonata', 1801, 15),
(2, 'Requiem in D minor, K.626', 'Requiem', 1791, 50),
(2, 'The Magic Flute, K.620', 'Opera', 1791, 160),
(3, 'Nocturne in E-flat major, Op.9-2', 'Nocturne', 1832, 5),
(3, 'Piano Concerto No.1 in E minor, Op.11', 'Piano Concerto', 1830, 40);
INSERT 0 6
classical_demo=# SELECT * FROM works;
id | composer_id | title | genre | year | duration_min
----+-------------+---------------------------------------+----------------+------+--------------
1 | 1 | Symphony No.5 in C minor, Op.67 | Symphony | 1808 | 35
2 | 1 | Piano Sonata No.14 "Moonlight" | Piano Sonata | 1801 | 15
3 | 2 | Requiem in D minor, K.626 | Requiem | 1791 | 50
4 | 2 | The Magic Flute, K.620 | Opera | 1791 | 160
5 | 3 | Nocturne in E-flat major, Op.9-2 | Nocturne | 1832 | 5
6 | 3 | Piano Concerto No.1 in E minor, Op.11 | Piano Concerto | 1830 | 40
(6 rows)6. JOINで「作曲家＋曲名」を一覧する
せっかくテーブルを分けたので、JOIN して一覧を出してみます。
classical_demo=# SELECT
w.title,
w.genre,
c.name AS composer,
w.year
FROM works w
JOIN composers c ON w.composer_id = c.id
ORDER BY w.year;
title | genre | composer | year
---------------------------------------+----------------+-------------------------+------
Requiem in D minor, K.626 | Requiem | Wolfgang Amadeus Mozart | 1791
The Magic Flute, K.620 | Opera | Wolfgang Amadeus Mozart | 1791
Piano Sonata No.14 "Moonlight" | Piano Sonata | Ludwig van Beethoven | 1801
Symphony No.5 in C minor, Op.67 | Symphony | Ludwig van Beethoven | 1808
Piano Concerto No.1 in E minor, Op.11 | Piano Concerto | Frédéric Chopin | 1830
Nocturne in E-flat major, Op.9-2 | Nocturne | Frédéric Chopin | 1832
(6 rows)ここまで来ると、
「自分で作ったクラシック音楽DBを psql で操作できている」 実感が湧いてきますね。
7. psqlらしい小技：\x（縦表示）と \timing
psql には、表示を少し便利にしてくれる小技もあります。
7-1. \x で縦表示にする
曲名が長い行を縦表示にすると、行を折り返さずに読めて便利です。
classical_demo=# \x
Expanded display is on.
classical_demo=# SELECT * FROM works WHERE title LIKE '%Piano%';
-[ RECORD 1 ]+--------------------------------------
id | 2
composer_id | 1
title | Piano Sonata No.14 "Moonlight"
genre | Piano Sonata
year | 1801
duration_min | 15
-[ RECORD 2 ]+--------------------------------------
id | 6
composer_id | 3
title | Piano Concerto No.1 in E minor, Op.11
genre | Piano Concerto
year | 1830
duration_min | 40
classical_demo=# \x
Expanded display is off.7-2. \timing でクエリ時間を表示する
クエリがどれくらいかかったか、簡単に測ることもできます。
classical_demo=# \timing on
Timing is on.
classical_demo=# SELECT * FROM works;
id | composer_id | title | genre | year | duration_min
----+-------------+---------------------------------------+----------------+------+--------------
1 | 1 | Symphony No.5 in C minor, Op.67 | Symphony | 1808 | 35
2 | 1 | Piano Sonata No.14 "Moonlight" | Piano Sonata | 1801 | 15
3 | 2 | Requiem in D minor, K.626 | Requiem | 1791 | 50
4 | 2 | The Magic Flute, K.620 | Opera | 1791 | 160
5 | 3 | Nocturne in E-flat major, Op.9-2 | Nocturne | 1832 | 5
6 | 3 | Piano Concerto No.1 in E minor, Op.11 | Piano Concerto | 1830 | 40
(6 rows)
Time: 1.068 ms
classical_demo=# \timing off
Timing is off.本番環境での軽いチェックや、
「インデックスを貼る前後でどれくらい違うか試してみる」といった用途にも使えます。
8. おわりに
今回は、psql だけを使って
- Docker 上の PostgreSQL に接続する
- メタコマンド（
\l,\c,\dt,\dなど）で中身を眺める - クラシック音楽のミニ DB を作る（
CREATE TABLE/INSERT/SELECT/JOIN） \xや\timingといった小技で見やすく・測りやすくする
ところまでを一気にやってみました。
GUI ツールも便利ですが、
「とりあえず SSH 先で psql を叩ける」 というだけで、
トラブルシュートや確認作業のスピードがだいぶ変わってきます。
次回は、psql の他のメタコマンド \dt+ や \di を使ってみたり、\copy を使って CSV に書き出してみたり、
.psqlrc を使って「いつも使う設定」を仕込んでおくあたりも触ってみようかなと思います。
読んでくださってありがとうございました。
それではまた！