PostgreSQLチートシート：ログインからスキーマ確認・DMLまとめ

PostgreSQLチートシート：ログインからスキーマ確認・DMLまとめ

はじめに

PostgreSQLはRailsの本番環境で広く使われるデータベースです。psql コマンドを使うとDBに直接ログインしてテーブル構造の確認やデータの操作ができます。

この記事ではログインから、スキーマ・テーブル定義の確認、よく使うSQLまでまとめます。

---

ログイン

psql -U ユーザー名 -d データベース名

psql -U postgres                         # postgresユーザーでデフォルトDB
psql -U postgres -d myapp_development    # DB指定
psql -h localhost -p 5432 -U app_user -d myapp   # ホスト・ポート指定
psql -U postgres -d myapp -c "SELECT COUNT(*) FROM users;"  # ワンライナー実行

接続すると データベース名=# プロンプトが表示されます。

psql (16.0)
Type "help" for help.

myapp_development=#

Railsのdatabase.ymlから接続情報を確認

# config/database.yml
development:
  adapter: postgresql
  host: localhost
  port: 5432
  database: myapp_development
  username: app_user
  password: password

接続URLで指定

psql postgresql://app_user:password@localhost:5432/myapp_development

---

メタコマンド（バックスラッシュコマンド）

PostgreSQLのメタコマンドは \ から始まります。SQLの終端 ; は不要です。

コマンド	説明
\?	メタコマンド一覧
\q	終了
\l	データベース一覧
\c データベース名	DBを切り替え
\dt	テーブル一覧
\dt パターン*	名前でフィルタ
\d テーブル名	テーブル定義（カラム・インデックス）
\d+ テーブル名	テーブル定義（詳細）
\di	インデックス一覧
\ds	シーケンス一覧
\dv	ビュー一覧
\dn	スキーマ一覧
\du	ユーザー・ロール一覧
\timing	実行時間の表示切替
\x	縦表示の切替（Expanded display）
\e	エディタでSQL編集
\i ファイル名	SQLファイルを読み込んで実行
\o ファイル名	出力先をファイルに変更
\copy	CSVのインポート・エクスポート

縦表示（\x）

カラム数が多いとき見やすくなります。

myapp=# \x
Expanded display is on.
myapp=# SELECT * FROM users LIMIT 1;
-[ RECORD 1 ]----+---------------------
id               | 1
name             | 田中太郎
email            | tanaka@example.com
created_at       | 2026-05-29 10:00:00
updated_at       | 2026-05-29 10:00:00

---

スキーマ・テーブル定義の確認

テーブル一覧

myapp=# \dt
          List of relations
 Schema |       Name        | Type  |  Owner
--------+-------------------+-------+----------
 public | schema_migrations | table | app_user
 public | users             | table | app_user
 public | posts             | table | app_user

テーブル定義を確認する

myapp=# \d users
                                        Table "public.users"
   Column   |              Type              | Nullable |      Default
------------+--------------------------------+----------+-------------------
 id         | bigint                         | not null | generated always
 name       | character varying(255)         | not null |
 email      | character varying(255)         | not null |
 created_at | timestamp(6) without time zone | not null |
 updated_at | timestamp(6) without time zone | not null |
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)
    "index_users_on_email" UNIQUE, btree (email)

information_schema でカラム詳細を確認

SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_name = 'users'
ORDER BY ordinal_position;

インデックス一覧

SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'users';

---

基本的なSQL

SELECT

SELECT * FROM users;
SELECT id, name, email FROM users;
SELECT * FROM users WHERE name = '田中太郎';
SELECT * FROM users WHERE email LIKE '%@example.com';
SELECT * FROM users WHERE created_at > '2026-01-01';
SELECT * FROM users LIMIT 10 OFFSET 20;
SELECT * FROM users ORDER BY created_at DESC;
SELECT COUNT(*) FROM users;
SELECT COUNT(*), DATE(created_at) AS date FROM users GROUP BY date ORDER BY date DESC;

JOIN

SELECT users.name, posts.title
FROM users
INNER JOIN posts ON posts.user_id = users.id;

SELECT posts.title, users.name
FROM posts
LEFT JOIN users ON users.id = posts.user_id;

INSERT

INSERT INTO users (name, email, created_at, updated_at)
VALUES ('田中太郎', 'tanaka@example.com', NOW(), NOW());

-- 複数行挿入
INSERT INTO users (name, email, created_at, updated_at) VALUES
  ('田中太郎', 'tanaka@example.com', NOW(), NOW()),
  ('鈴木花子', 'suzuki@example.com', NOW(), NOW());

UPDATE

UPDATE users SET name = '田中次郎' WHERE id = 1;
UPDATE users SET updated_at = NOW() WHERE email LIKE '%@example.com';

DELETE

DELETE FROM users WHERE id = 1;
DELETE FROM users WHERE created_at < '2025-01-01';
TRUNCATE TABLE users;           -- 全件削除（高速・ロールバック可）
TRUNCATE TABLE users RESTART IDENTITY;  -- シーケンスもリセット

---

PostgreSQL特有の機能

RETURNING — 挿入・更新結果を返す

INSERT INTO users (name, email, created_at, updated_at)
VALUES ('田中太郎', 'tanaka@example.com', NOW(), NOW())
RETURNING id, name;

UPDATE users SET name = '田中次郎' WHERE id = 1
RETURNING id, name, updated_at;

EXPLAIN ANALYZE — 実行計画と実際の実行時間

EXPLAIN SELECT * FROM users WHERE email = 'tanaka@example.com';
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'tanaka@example.com';

Index Scan using index_users_on_email on users  (cost=0.28..8.29 rows=1 width=...)
  Index Cond: ((email)::text = 'tanaka@example.com'::text)
Planning Time: 0.1 ms
Execution Time: 0.05 ms

• Seq Scan（全件スキャン）より Index Scan の方が高速
• ANALYZE を付けると実際の実行時間も表示される

JSONB型

-- JSONB カラムへのアクセス
SELECT data->>'name' FROM profiles WHERE data->>'role' = 'admin';
SELECT data->'address'->>'city' FROM profiles;

-- JSONB の検索
SELECT * FROM profiles WHERE data @> '{"role": "admin"}';

-- インデックス（GINインデックス）
CREATE INDEX idx_profiles_data ON profiles USING gin(data);

配列型

SELECT * FROM posts WHERE 'ruby' = ANY(tags);
SELECT * FROM posts WHERE tags @> ARRAY['ruby', 'rails'];
SELECT array_agg(name) FROM users;

WITH句（CTE）

複雑なクエリを整理するときに使います。

WITH active_users AS (
  SELECT id, name FROM users WHERE active = true
),
recent_posts AS (
  SELECT user_id, COUNT(*) AS post_count
  FROM posts
  WHERE created_at > NOW() - INTERVAL '30 days'
  GROUP BY user_id
)
SELECT u.name, p.post_count
FROM active_users u
INNER JOIN recent_posts p ON p.user_id = u.id
ORDER BY p.post_count DESC;

WINDOW関数

SELECT name,
       COUNT(*) OVER (PARTITION BY role) AS role_count,
       ROW_NUMBER() OVER (ORDER BY created_at) AS row_num
FROM users;

日時関数

SELECT NOW();                                          -- 現在日時（タイムゾーン付き）
SELECT CURRENT_DATE;                                   -- 現在日付
SELECT NOW() - INTERVAL '7 days';                     -- 7日前
SELECT DATE_TRUNC('month', created_at) FROM users;    -- 月単位に切り捨て
SELECT TO_CHAR(created_at, 'YYYY-MM-DD') FROM users;  -- フォーマット
SELECT EXTRACT(YEAR FROM created_at) FROM users;       -- 年を取り出す

---

バックアップ・リストア

# バックアップ
pg_dump -U postgres myapp_development > backup.sql
pg_dump -U postgres -Fc myapp_development > backup.dump   # カスタム形式（推奨）

# リストア
psql -U postgres myapp_development < backup.sql
pg_restore -U postgres -d myapp_development backup.dump

---

Railsでよく使うパターン

psql -U postgres myapp_development

-- マイグレーション履歴を確認
SELECT version FROM schema_migrations ORDER BY version DESC LIMIT 5;

-- テーブルサイズを確認
SELECT relname, pg_size_pretty(pg_total_relation_size(relid))
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

---

まとめ

操作	コマンド
ログイン	psql -U ユーザー名 -d DB名
DB一覧	\l
DB切替	\c DB名
テーブル一覧	\dt
テーブル定義	\d テーブル名
縦表示切替	\x
実行計画	EXPLAIN ANALYZE SELECT ...;
終了	\q

SQLiteでの同様の操作は「SQLiteチートシート：ログインからスキーマ確認・DMLまとめ」を参照してください。

MySQLでの同様の操作は「MySQLチートシート：ログインからスキーマ確認・DMLまとめ」を参照してください。

Active RecordからSQLがどう発行されるかは「Active Record入門：検索・更新・リレーションの基本まとめ」を参照してください。

RailsのDBマイグレーション操作は「Rails のDB操作コマンド入門：db:create から db:migrate まで」を参照してください。