PostgreSQL チートシート：開発者のためのクイックリファレンス

PostgreSQL 早わかり

目次

日々の作業で役立つ、 PostgreSQL のクイックリファレンス：接続、SQL構文、psql メタコマンド、パフォーマンス、JSON、ウィンドウ関数など。

Postgres の詳細に入る前に、データベース横断的な簡潔な復習をしたい場合は、最も便利なSQLコマンドのSQLチートシート が便利な仲間です。

postgresql logo

接続と基礎

# 接続
psql -h HOST -p 5432 -U USER -d DB
psql $DATABASE_URL

# psql 内部で
\conninfo             -- 接続情報表示
\l[+]                 -- データベースの一覧表示
\c DB                 -- DB に接続
\dt[+] [schema.]pat   -- テーブルの一覧表示
\dv[+]                -- ビューの一覧表示
\ds[+]                -- シーケンスの一覧表示
\df[+] [pat]          -- 関数の一覧表示
\d[S+] name           -- テーブル/ビュー/シーケンスの詳細表示
\dn[+]                -- スキーマの一覧表示
\du[+]                -- ロールの一覧表示
\timing               -- クエリ計時の切り替え
\x                    -- 拡張表示
\e                    - $EDITOR でバッファを編集
\i file.sql           -- ファイル実行
\copy ...             -- クライアント側の COPY
\! shell_cmd          -- シェルコマンド実行

データ型（一般的なもの）

  • 数値: smallint, integer, bigint, decimal(p,s), numeric, real, double precision, serial, bigserial
  • テキスト: text, varchar(n), char(n)
  • 論理型: boolean
  • 時間日付: timestamp [with/without time zone], date, time, interval
  • UUID: uuid
  • JSON: json, jsonb (推奨)
  • 配列: type[] 例: text[]
  • ネットワーク: inet, cidr, macaddr
  • 幾何学: point, line, polygon など

PostgreSQL バージョンの確認

SELECT version();

PostgreSQL サーバーバージョン:

pg_config --version

PostgreSQL クライアントバージョン:

psql --version

DDL (作成 / 変更)

-- スキーマとテーブルの作成
CREATE SCHEMA IF NOT EXISTS app;
CREATE TABLE app.users (
  id           bigserial PRIMARY KEY,
  email        text NOT NULL UNIQUE,
  name         text,
  active       boolean NOT NULL DEFAULT true,
  created_at   timestamptz NOT NULL DEFAULT now(),
  profile      jsonb,
  tags         text[]
);

-- 変更
ALTER TABLE app.users ADD COLUMN last_login timestamptz;
ALTER TABLE app.users ALTER COLUMN name SET NOT NULL;
ALTER TABLE app.users DROP COLUMN tags;

-- 制約
ALTER TABLE app.users ADD CONSTRAINT email_lower_uk UNIQUE (lower(email));

-- インデックス
CREATE INDEX ON app.users (email);
CREATE UNIQUE INDEX CONCURRENTLY users_email_uidx ON app.users (lower(email));
CREATE INDEX users_profile_gin ON app.users USING gin (profile);
CREATE INDEX users_created_at_idx ON app.users (created_at DESC);

DML (挿入 / 更新 / апsert / 削除)

INSERT INTO app.users (email, name) VALUES
  ('a@x.com','A'), ('b@x.com','B')
RETURNING id;

-- Upsert (ON CONFLICT)
INSERT INTO app.users (email, name)
VALUES ('a@x.com','Alice')
ON CONFLICT (email)
DO UPDATE SET name = EXCLUDED.name, updated_at = now();

UPDATE app.users SET active = false WHERE last_login < now() - interval '1 year';
DELETE FROM app.users WHERE active = false AND last_login IS NULL;

クエリの基本

SELECT * FROM app.users ORDER BY created_at DESC LIMIT 20 OFFSET 40; -- ページング

-- フィルタリング
SELECT * FROM app.users WHERE email ILIKE '%@example.%' AND active;

-- 集約と GROUP BY
SELECT active, count(*) AS n
FROM app.users
GROUP BY active
HAVING count(*) > 10;

-- JOIN
SELECT o.id, u.email, o.total
FROM app.orders o
JOIN app.users u ON u.id = o.user_id
LEFT JOIN app.discounts d ON d.id = o.discount_id;

-- DISTINCT ON (Postgres 固有)
SELECT DISTINCT ON (user_id) user_id, status, created_at
FROM app.events
ORDER BY user_id, created_at DESC;

-- CTE (共通テーブル式)
WITH recent AS (
  SELECT * FROM app.orders WHERE created_at > now() - interval '30 days'
)
SELECT count(*) FROM recent;

-- 再帰 CTE
WITH RECURSIVE t(n) AS (
  SELECT 1
  UNION ALL
  SELECT n+1 FROM t WHERE n < 10
)
SELECT sum(n) FROM t;

ウィンドウ関数

SELECT
  user_id,
  created_at,
  sum(total)      OVER (PARTITION BY user_id ORDER BY created_at) AS running_total,
  row_number()    OVER (PARTITION BY user_id ORDER BY created_at) AS rn,
  lag(total, 1)   OVER (PARTITION BY user_id ORDER BY created_at) AS prev_total
FROM app.orders;

JSON / JSONB

-- 抽出
SELECT profile->>'company' AS company FROM app.users;
SELECT profile->'address'->>'city' FROM app.users;

-- jsonb クエリ用インデックス
CREATE INDEX users_profile_company_gin ON app.users USING gin ((profile->>'company'));

-- 存在 / 包含
SELECT * FROM app.users WHERE profile ? 'company';              -- キーが存在するか
SELECT * FROM app.users WHERE profile @> '{"role":"admin"}'; -- 包含

-- jsonb の更新
UPDATE app.users
SET profile = jsonb_set(COALESCE(profile,'{}'::jsonb), '{prefs,theme}', '"dark"', true);

配列

-- 所属と包含
SELECT * FROM app.users WHERE 'vip' = ANY(tags);
SELECT * FROM app.users WHERE tags @> ARRAY['beta'];

-- 追加
UPDATE app.users SET tags = array_distinct(tags || ARRAY['vip']);

時間と日付

SELECT now() AT TIME ZONE 'Australia/Melbourne';
SELECT date_trunc('day', created_at) AS d, count(*)
FROM app.users GROUP BY d ORDER BY d;

-- インターバル
SELECT now() - interval '7 days';

トランザクションとロック

BEGIN;
UPDATE app.accounts SET balance = balance - 100 WHERE id = 1;
UPDATE app.accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; -- または ROLLBACK

-- イソレーションレベル
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- ロックの確認
SELECT * FROM pg_locks l JOIN pg_stat_activity a USING (pid);

ロールと権限

-- ロール/ユーザーの作成
CREATE ROLE app_user LOGIN PASSWORD 'secret';
GRANT USAGE ON SCHEMA app TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO app_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA app GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;

インポート / エクスポート

-- サーバー側 (スーパーユーザーまたは適切な権限が必要)
COPY app.users TO '/tmp/users.csv' CSV HEADER;
COPY app.users(email,name) FROM '/tmp/users.csv' CSV HEADER;

-- クライアント側 (psql)
\copy app.users TO 'users.csv' CSV HEADER
\copy app.users(email,name) FROM 'users.csv' CSV HEADER

パフォーマンスと観測性

EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT ...;  -- 実際の実行時間

-- 統計ビュー
SELECT * FROM pg_stat_user_tables;
SELECT * FROM pg_stat_statements ORDER BY total_time DESC LIMIT 20; -- 拡張機能が必要

-- メンテナンス
VACUUM [FULL] [VERBOSE] table_name;
ANALYZE table_name;
REINDEX TABLE table_name;

Docker、Git、PostgreSQL を含む必須開発ツールのより広い概要については、開発ツール: モダンな開発ワークフロー完全ガイド を参照してください。

必要に応じて拡張機能を有効にします:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS btree_gin;
CREATE EXTENSION IF NOT EXISTS pg_trgm; -- トリグラム検索

フルテキスト検索 (クイック)

ALTER TABLE app.docs ADD COLUMN tsv tsvector;
UPDATE app.docs SET tsv = to_tsvector('english', coalesce(title,'') || ' ' || coalesce(body,''));
CREATE INDEX docs_tsv_idx ON app.docs USING gin(tsv);
SELECT id FROM app.docs WHERE tsv @@ plainto_tsquery('english', 'quick brown fox');

ネイティブ Postgres 検索が十分なのか、それとも別個の検索スタックを動かす必要があるのかを決めている場合は、PostgreSQL フルテキスト検索 vs Elasticsearch 比較 が、トレードオフを詳細に解説しています。

便利な psql 設定

\pset pager off       -- ページャーを無効化
\pset null '∅'
\pset format aligned  -- その他: unaligned, csv
\set ON_ERROR_STOP on
\timing on

便利なカタログクエリ

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

-- インデックスブloat (概算)
SELECT schemaname, relname, indexrelname, idx_scan
FROM pg_stat_user_indexes ORDER BY idx_scan ASC NULLS FIRST LIMIT 20;

バックアップ / リストア

# 論理ダンプ
pg_dump -h HOST -U USER -d DB -F c -f db.dump        # カスタム形式
pg_restore -h HOST -U USER -d NEW_DB -j 4 db.dump

# プレーンSQL
pg_dump -h HOST -U USER -d DB > dump.sql
psql -h HOST -U USER -d DB -f dump.sql

GUI データベース管理ツールについては、DBeaver vs Beekeeper - SQL データベース管理ツール と Linux への DBeaver インストール - howto を参照してください。

リポジトリやファイルのバックアップと並べて pg_dump/psql を使用した実例については、Gitea サーバーのバックアップとリストア をご覧ください。

レプリケーション (ハイレベル)

  • WAL ファイルは プライマリ → スタンバイ にストリームされる
  • 主な設定: wal_level, max_wal_senders, hot_standby, primary_conninfo
  • ツール: pg_basebackup, standby.signal (PG ≥12)

注意点とTips

  • インデックスと演算子の使用には、必ず jsonb を使用し、json は避けること。
  • timestamptz (タイムゾーン対応) を優先すること。
  • DISTINCT ON は「グループごとのトップ N」を取得する Postgres の強み。
  • serial の代わりに GENERATED ALWAYS AS IDENTITY を使用すること。
  • 本番環境のクエリで SELECT * を避けること。
  • 選択度の高いフィルター条件と結合キーのためにインデックスを作成すること。
  • 最適化する前に EXPLAIN (ANALYZE) で計測すること。

バージョン固有の便利な機能 (≥v12+)

-- 生成されたアイデンティティ
CREATE TABLE t (id bigINT GENERATED ALWAYS AS IDENTITY, ...);

-- 部分インデックス付き UPSERT
CREATE UNIQUE INDEX ON t (key) WHERE is_active;

関連リンク

購読する

システム、インフラ、AIエンジニアリングの新記事をお届けします。