PostgreSQL 入門: 動かして学ぶチュートリアル
ページ上で実行できるクエリで学ぶ無料の PostgreSQL 入門です。データ型、CREATE TABLE、upsert、JSONB、日付、CTE、ウィンドウ関数、インデックス、VACUUM を扱います。クエリを書き換えてもう一度実行できます。
コースを始めるはじめに
- PostgreSQL vs MySQLPostgreSQL と MySQL はどちらも無料のリレーショナルデータベースです。PostgreSQL は SQL の機能、JSONB、トランザクション内の DDL が充実しています。MySQL はすでに標準になっている環境なら運用が簡単です。
- PostgreSQL vs SQL ServerPostgreSQL は無料のオープンソースです。SQL Server は T-SQL と Windows 中心のツール群を持つ Microsoft の商用データベースです。SQL、JSON、ロック、ライセンスの違いをまとめます。
- PostgreSQL vs MongoDBPostgreSQL は決まったスキーマのテーブルに行を保存し、jsonb 列に JSON ドキュメントも持てます。MongoDB はスキーマが柔軟なドキュメントをコレクションに保存し、複数のサーバーにシャーディングします。
- データベースとテーブルの一覧psql では \l でデータベースの一覧、\c で接続先の切り替え、\dt でテーブルの一覧を表示します。SQL では pg_database、information_schema.tables、information_schema.columns を検索します。
テーブルと型
- データ型ほぼすべての列で使う PostgreSQL のデータ型をまとめます。整数、numeric、text、boolean、timestamptz、uuid、jsonb、配列、enum と、それぞれを選ぶ場面です。
- CREATE TABLECREATE TABLE はテーブルの列、型、制約を定義します。identity のキー、NOT NULL、DEFAULT、UNIQUE、CHECK、IF NOT EXISTS、CREATE TABLE AS、LIKE を扱います。
- ALTER TABLEALTER TABLE は既存のテーブルを変更します。列の追加、名前変更、削除、USING を使った型の変更、デフォルト値と NOT NULL の設定、制約の追加を扱います。
- 主キーPostgreSQL で主キーを定義する方法と、その値の生成方法を解説します。identity 列、従来の SERIAL、gen_random_uuid() による UUID、複合キー、シーケンスのリセットを扱います。
- 外部キー外部キーを使うと、列は別のテーブルに存在する値しか受け付けなくなります。REFERENCES、ON DELETE CASCADE、SET NULL、RESTRICT、既存のテーブルへのキーの追加、インデックスの作成を扱います。
- enum 型enum 型は、許可する値を順序付きで固定したリストで、CREATE TYPE ... AS ENUM で作ります。値の追加と名前変更、一覧の表示、並べ替え、CHECK 制約のほうが向いている場面を扱います。
- タイムスタンプtimestamptz は時点を保存し、セッションのタイムゾーンで表示します。timestamp はタイムゾーンのない時計の時刻を保存します。どちらも入力したタイムゾーンは保存しません。ほぼすべての場面で timestamptz を使ってください。
クエリ
- JOINJOIN は条件に従って 2 つのテーブルの行を組み合わせます。INNER JOIN は一致した行、LEFT JOIN は最初のテーブルのすべての行、FULL JOIN は両方の行を残し、CROSS JOIN はすべてを組み合わせ、LATERAL は行ごとにサブクエリを実行します。
- DISTINCT ONSELECT DISTINCT ON (key) ... ORDER BY key, sort は、顧客ごとの最新の注文のように、各グループの最初の行を残します。PostgreSQL だけの機能です。
- COALESCE、NULLIF、CASECOALESCE(a, b, ...) は NULL でない最初の引数を返し、NULLIF(a, b) は値を NULL に変え、CASE WHEN は条件に応じて値を選びます。
- LIKE、ILIKE、正規表現LIKE は % と _ を使ったパターンで文字列を照合し、ILIKE は大文字と小文字を区別せずに同じ照合を行います。~ 系の演算子は POSIX 正規表現で照合します。
- CAST と ::PostgreSQL では CAST(value AS type) と value::type で値を別の型に変換します。どちらも同じ変換で、:: は PostgreSQL の短い書き方です。
データの変更
- upsert (ON CONFLICT)PostgreSQL の upsert は INSERT ... ON CONFLICT で書きます。EXCLUDED を使う DO UPDATE、DO NOTHING、条件付きの更新、カウンター、挿入された行と更新された行の見分け方を扱います。
- RETURNINGRETURNING を付けると INSERT、UPDATE、DELETE、MERGE が変更した行を返します。採番された id、更新後の値、削除した行、MERGE が実行した処理まで取得できます。
- トランザクションPostgreSQL のトランザクションの仕組みを解説します。BEGIN、COMMIT、ROLLBACK、セーブポイント、エラーが起きたときの挙動、分離レベルまで押さえます。
JSON と配列
- JSONBPostgreSQL の jsonb で JSON を保存して検索する方法。-> と ->> 演算子、@> による包含検索、jsonb_set などでの更新、配列を行に展開する方法、GIN インデックス、json と jsonb の違いを解説します。
- 配列PostgreSQL の配列型の列を解説します。宣言のしかた、1 から始まる添字、ANY、@>、&& で値が含まれるか調べる方法、unnest、よく使う配列関数まで押さえます。
- string_agg と array_aggstring_agg はグループの値を1つの文字列に連結し、array_agg は配列にまとめます。並び順、DISTINCT、FILTER、NULL の扱い、よくあるエラーを解説します。
文字列と日付
- 文字列関数PostgreSQL で毎日使う文字列関数をまとめました。substring、left と right、|| と concat、split_part、position、replace、regexp_replace、trim、lpad、format を解説します。
- 日付関数PostgreSQL で日付を扱う方法。now() と current_date、interval による加算と減算、集計に使う date_trunc、extract と age、to_char での書式指定まで解説します。
- generate_seriesgenerate_series は開始値から終了値までの行の集合を返します。数値、日付、タイムスタンプに使え、レポートの欠けた期間の補完、テストデータの生成、カレンダー作りに役立ちます。
高度なクエリ
パフォーマンスと保守
- インデックスCREATE INDEX はデフォルトで B-tree インデックスを作り、PostgreSQL がテーブル全体を読まずに行を探せるようにします。ほかの種類 (GIN、GiST、BRIN、hash) は JSONB、配列、全文検索、範囲型、巨大なテーブルに使います。
- EXPLAIN ANALYZEEXPLAIN は PostgreSQL がクエリに選んだ実行計画を表示し、EXPLAIN ANALYZE はクエリを実行して各ステップの実際の行数と時間を加えます。計画ツリーの読み方とよく出るノード名を覚えましょう。
- ビューとマテリアライズドビュービューは保存したクエリで、読むたびに実行されます。マテリアライズドビューはクエリの結果を保存し、REFRESH MATERIALIZED VIEW を実行したときだけ変わります。
- VACUUMVACUUM は UPDATE と DELETE が残した不要な行バージョンを取り除き、その領域を再利用できるようにします。autovacuum が自動で実行してくれます。実行のタイミング、調整方法、VACUUM FULL のロックに見合う場面を解説します。