データベース操作といえばGUIツール（画面でクリックして操作するツール）が主流になりつつありますが、より細かく正確な操作を行うには、コマンドラインツールの理解が不可欠です。
中でも、PostgreSQLを扱うなら「psql（ピー・エス・キュー・エル）」の習得は避けて通れません。
この記事では、psqlの基本的なつかい方から、よくつかわれるコマンド、便利なオプションまで、初心者でも理解できるようにやさしく説明します。
psqlってなに？
説明
psqlとは、PostgreSQLに標準で付いている対話型のコマンドラインクライアントです。
「対話型」というのは、コマンドを入力するとすぐに結果が返ってくることを意味します。
まるでデータベースと会話しているような感覚でつかえます。
psqlでできること
基本的な操作
- SQLを直接入力して実行
- テーブル構造の確認
- データの表示・追加・変更・削除
- データベースの作成・削除
高度な操作
- データのインポート・エクスポート
- バックアップの作成
- シェルスクリプトやバッチ処理
- 設定の変更
なぜpsqlを覚える必要があるの？
GUIツールとの違い
| 項目 | psql（コマンドライン） | GUIツール |
|---|---|---|
| 操作速度 | 慣れれば非常に速い | クリック操作で時間がかかる |
| 自動化 | スクリプト化できる | 手動操作が必要 |
| 細かい制御 | すべての機能にアクセス可能 | 一部機能に制限あり |
| サーバー接続 | SSH経由でも軽快 | ネットワーク負荷が大きい |
PostgreSQLとpsqlの環境準備
PostgreSQLのインストール確認
まず、あなたのパソコンにPostgreSQLがインストールされているか確認しましょう。
Windowsの場合
psql --version
Mac/Linuxの場合
psql --version
正常な結果の例
psql (PostgreSQL) 15.4
PostgreSQLがインストールされていない場合
Windows
- PostgreSQL公式サイトからダウンロード
- インストーラーを実行
- デフォルト設定でインストール
Mac（Homebrewつかう場合）
brew install postgresql
brew services start postgresql
Ubuntu/Debian
sudo apt update
sudo apt install postgresql postgresql-client
psqlの起動と接続方法
基本的な接続方法
基本構文
psql -U ユーザー名 -d データベース名 -h ホスト名 -p ポート番号
主要オプション
| オプション | 説明 | 例 |
|---|---|---|
-U | ユーザー名を指定 | -U postgres |
-d | データベース名を指定 | -d mydb |
-h | ホスト名を指定 | -h localhost |
-p | ポート番号を指定 | -p 5432 |
-W | パスワード入力を強制 | -W |
実際の接続例
例1：ローカルのデータベースに接続
psql -U postgres -d postgres
例2：リモートサーバーに接続
psql -U myuser -d mydatabase -h 192.168.1.100 -p 5432
例3：パスワード入力を強制
psql -U postgres -d mydatabase -W
接続成功時の画面
接続に成功すると、以下のような画面が表示されます：
psql (15.4)
Type "help" for help.
postgres=#
この postgres=# がpsqlのプロンプトです。ここにコマンドやSQLを入力します。
接続時によくあるエラーと解決方法
エラー1：「connection refused」
psql: error: connection to server at "localhost" (127.0.0.1), port 5432 failed: Connection refused
原因と解決策
- PostgreSQLサーバーが起動していない
- サービスを開始：
sudo systemctl start postgresql（Linux）
エラー2：「authentication failed」
psql: error: connection to server at "localhost" (127.0.0.1), port 5432 failed: FATAL: password authentication failed for user "postgres"
原因と解決策
- パスワードが間違っている
- 正しいパスワードを確認するか、パスワードをリセット
psqlの基本コマンド
データベース操作のコマンド
psqlには「メタコマンド」という特別なコマンドがあります。これらは \（バックスラッシュ）で始まります。
データベース一覧の表示
\l
実行結果の例
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
myapp | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
データベースの切り替え
\c データベース名
例：
\c myapp
テーブル操作のコマンド
テーブル一覧の表示
\dt
実行結果の例
List of relations
Schema | Name | Type | Owner
--------+----------+-------+----------
public | products | table | postgres
public | users | table | postgres
public | orders | table | postgres
テーブル構造の確認
\d テーブル名
例：
\d users
実行結果の例
Table "public.users"
Column | Type | Collation | Nullable | Default
--------+------------------------+-----------+----------+---------
id | integer | | not null | nextval('users_id_seq'::regclass)
name | character varying(100) | | not null |
email | character varying(255) | | not null |
age | integer | | |
Indexes:
"users_pkey" PRIMARY KEY, btree (id)
ユーザーと権限の確認
ユーザー一覧の表示
\du
現在のユーザー確認
\conninfo
実行結果の例
You are connected to database "myapp" as user "postgres" via socket in "/var/run/postgresql" at port "5432".
SQLの実行
基本的なSQL文の実行
psqlでは、通常のSQL文をそのまま入力して実行できます。
データの検索
SELECT * FROM users;
特定のカラムだけ取得
SELECT name, email FROM users WHERE age > 25;
データの追加
INSERT INTO users (name, email, age) VALUES ('田中太郎', 'tanaka@example.com', 30);
データの更新
UPDATE users SET age = 31 WHERE name = '田中太郎';
データの削除
DELETE FROM users WHERE age < 18;
複数行のSQL文
長いSQL文は複数行に分けて入力できます：
SELECT u.name, u.email, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.age >= 20
GROUP BY u.id, u.name, u.email
ORDER BY order_count DESC;
SQL文の最後に ;（セミコロン）をつけてEnterキーを押すと実行されます。
psqlの便利な機能
コマンド履歴と補完
コマンド履歴
- ↑キー：前のコマンド
- ↓キー：次のコマンド
TAB補完
SELECT * FROM us[TAB] -- "users"に自動補完
\d user[TAB] -- "users"に自動補完
実行結果の表示設定
拡張表示モード
\x
これをオンにすると、テーブルが縦方向に表示されて見やすくなります：
-[ RECORD 1 ]--+------------------------
id | 1
name | 田中太郎
email | tanaka@example.com
age | 30
ページング設定
\pset pager off -- ページングを無効
\pset pager on -- ページングを有効
ファイルからSQLを実行
SQLファイルの実行
\i /path/to/script.sql
外部ファイルの例（init.sql）
-- データベースの初期化
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(255) NOT NULL UNIQUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO users (name, email) VALUES
('田中太郎', 'tanaka@example.com'),
('佐藤花子', 'sato@example.com'),
('山田次郎', 'yamada@example.com');
実行結果をファイルに出力
結果をファイルに保存
\o output.txt
SELECT * FROM users;
\o -- ファイル出力を終了
CSV形式で出力
\copy users TO 'users.csv' CSV HEADER;
コマンドラインからの直接実行
一回だけSQL実行
psqlに接続せずに、コマンドラインから直接SQLを実行できます：
psql -U postgres -d mydb -c "SELECT COUNT(*) FROM users;"
SQLファイルを実行
psql -U postgres -d mydb -f init.sql
結果をファイルに出力
psql -U postgres -d mydb -c "SELECT * FROM users;" > users_data.txt
よくつかうメタコマンド一覧
データベース関連
| コマンド | 説明 | 例 |
|---|---|---|
\l | データベース一覧 | \l |
\c dbname | データベース切り替え | \c myapp |
\conninfo | 接続情報表示 | \conninfo |
テーブル関連
| コマンド | 説明 | 例 |
|---|---|---|
\dt | テーブル一覧 | \dt |
\d tablename | テーブル構造表示 | \d users |
\di | インデックス一覧 | \di |
\dv | ビュー一覧 | \dv |
ユーザー・権限関連
| コマンド | 説明 | 例 |
|---|---|---|
\du | ユーザー一覧 | \du |
\dp | テーブル権限表示 | \dp users |
ファイル操作
| コマンド | 説明 | 例 |
|---|---|---|
\i filename | SQLファイル実行 | \i init.sql |
\o filename | 出力をファイルに | \o result.txt |
\copy | データのCSVエクスポート | \copy users TO 'users.csv' CSV |
表示設定
| コマンド | 説明 | 例 |
|---|---|---|
\x | 拡張表示の切り替え | \x |
\timing | 実行時間表示の切り替え | \timing |
\q | psqlを終了 | \q |
実践的な使用例
データベースの初期セットアップ
-- 新しいデータベースを作成
CREATE DATABASE myapp;
-- データベースに切り替え
\c myapp
-- テーブルを作成
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(255) NOT NULL UNIQUE,
age INTEGER,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- サンプルデータを挿入
INSERT INTO users (name, email, age) VALUES
('田中太郎', 'tanaka@example.com', 28),
('佐藤花子', 'sato@example.com', 32),
('山田次郎', 'yamada@example.com', 25);
-- データを確認
SELECT * FROM users;
データの分析
-- 拡張表示をオンにする
\x
-- 実行時間表示をオンにする
\timing
-- 年齢別の統計
SELECT
CASE
WHEN age < 20 THEN '10代'
WHEN age < 30 THEN '20代'
WHEN age < 40 THEN '30代'
ELSE '40代以上'
END as age_group,
COUNT(*) as count
FROM users
GROUP BY age_group
ORDER BY age_group;
-- 結果をファイルに保存
\o user_stats.txt
SELECT name, email, age FROM users ORDER BY age;
\o
バックアップとリストア
データのエクスポート
\copy users TO 'users_backup.csv' CSV HEADER;
データのインポート
\copy users FROM 'users_backup.csv' CSV HEADER;
トラブルシューティング
よくあるエラーと解決方法
エラー1：SQL文が終わらない
mydb(#
原因：セミコロン（;）を忘れている 解決：; を入力してEnterキーを押す
エラー2：長い出力が画面に収まらない 解決：
\pset pager on -- ページング機能をオンにする
エラー3：日本語が文字化けする 解決：
# 接続時にエンコーディングを指定
psql -U postgres -d mydb --set=client_encoding=UTF8
パフォーマンスの確認
実行計画の表示
EXPLAIN SELECT * FROM users WHERE age > 25;
詳細な実行計画
EXPLAIN ANALYZE SELECT * FROM users WHERE age > 25;
まとめ
psqlの重要ポイント
- 基本接続：
psql -U ユーザー名 -d データベース名 - メタコマンド：
\で始まる特別なコマンド - SQL実行：通常のSQL文をそのまま実行可能
- 便利機能：TAB補完、履歴、ファイル入出力
学習のステップ
- 基本操作を覚える：接続、データベース一覧、テーブル一覧
- SQLを実行してみる：SELECT、INSERT、UPDATE、DELETE
- メタコマンドを活用：\d、\dt、\l などを覚える
- ファイル操作を試す：SQLファイルの実行、結果の出力
- 自動化に挑戦：スクリプト化、バッチ処理
コメント