PostgreSQL入門 | Mityの趣味のサイト

PostgreSQLとは

PostgreSQLは、SQLを使ってデータを管理するオープンソースのリレーショナルデータベース管理システムです。テーブル、主キー、外部キー、トランザクションなどを使い、アプリケーションのデータを安全に扱います。

PostgreSQLで意識すること

サーバー、データベース、スキーマ、テーブル、ロールを区別する
SQLを実行する前に、対象と結果を予想する
制約、権限、バックアップまで含めてデータを守る

PostgreSQLには標準SQLに近い部分と、RETURNING、JSONB、配列、全文検索などの拡張機能があります。まず共通のSQLを学び、必要になったらPostgreSQL固有の機能へ進みます。

環境を準備する

公式パッケージ、各OS用インストーラー、DockerなどでPostgreSQLを用意できます。学習では専用の練習用データベースを使い、本番データへ接続しません。

接続後に確認するPSQL

SELECT version();
SELECT current_database();
SELECT current_schema();
\conninfo

実行結果の例OUTPUT

current_database
study_postgres

current_schema
public

You are connected to database "study_postgres" as user "study_user".

環境構築は環境構築と接続で、psql、データベース、ロール、スキーマを順番に確認します。

SQLの基本

CREATE TABLEで表を作り、INSERTで行を追加し、SELECTで検索します。PostgreSQLではGENERATED BY DEFAULT AS IDENTITYを使うと、連番の主キーを標準SQLに近い形で定義できます。

商品テーブルとデータSQL

CREATE TABLE products (
    id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name text NOT NULL,
    price numeric(10, 2) NOT NULL CHECK (price >= 0),
    stock integer NOT NULL DEFAULT 0
);

INSERT INTO products (name, price, stock) VALUES
    ('PostgreSQL入門書', 3280.00, 5),
    ('ノート', 300.00, 12),
    ('ボールペン', 150.00, 0),
    ('マグカップ', 900.00, 3);

SELECT id, name, price, stock
FROM products
ORDER BY id;

実行結果OUTPUT

id  name              price    stock
1   PostgreSQL入門書  3280.00  5
2   ノート            300.00   12
3   ボールペン        150.00   0
4   マグカップ        900.00   3

金額はnumericで保存します。realやdouble precisionは近似値になるため、金額のように正確さが必要な値では目的を確認して使い分けます。

検索・集計・結合

WHEREで行を絞り、ORDER BYで並べ替え、GROUP BYで集計します。複数の表を読むときはJOINを使います。

在庫がある商品を価格順に表示SQL

SELECT name, price
FROM products
WHERE stock > 0
ORDER BY price DESC
LIMIT 2;

実行結果OUTPUT

name              price
PostgreSQL入門書  3280.00
マグカップ        900.00

読む順番 FROMで表、WHEREで行、SELECTで列、ORDER BYで順序を確認します。詳しくは検索・集計・結合へ進みましょう。

型と設計

PostgreSQLにはinteger、numeric、text、date、timestamp、jsonbなどの型があります。列の意味に合う型を選び、NOT NULL、UNIQUE、CHECK、外部キーでデータのルールを定義します。

商品、注文、注文詳細を一つの表へ詰め込まず、役割の異なる表へ分けると、重複や更新漏れを減らせます。型・設計・制約でサンプルを確認します。

安全な更新

注文登録と在庫更新のような複数操作は、トランザクションでまとめます。成功したらCOMMIT、失敗したらROLLBACKです。

更新を確認して取り消すSQL

BEGIN;
UPDATE products
SET stock = stock - 1
WHERE id = 1 AND stock >= 1;
SELECT id, name, stock FROM products WHERE id = 1;
ROLLBACK;

PostgreSQLでは、同時実行時のデータの見え方をMVCCが支えています。詳しくは更新とトランザクションで学びます。

アプリケーションから使う

PHPから接続するときはPDOのPostgreSQLドライバーを使い、SQLへ値を文字列連結せずプレースホルダーへ渡します。

PHP PDOで検索するPHP

$pdo = new PDO($dsn, $user, $password, [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);
$stmt = $pdo->prepare(
    'SELECT name, price FROM products WHERE price >= :price'
);
$stmt->execute(['price' => 500]);
$products = $stmt->fetchAll(PDO::FETCH_ASSOC);

接続、入力検証、トランザクション、エラー処理はPHPからPostgreSQLを使うで詳しく扱います。

次に学ぶこと

この講座は、環境構築、基本SQL、検索、設計、更新、アプリ連携、練習問題の順に進みます。まず短い例を実行し、その後でJSONB、配列、ビュー、ウィンドウ関数、全文検索、性能改善など目的に合う機能を選びます。

PostgreSQL公式ドキュメント現在の仕様と詳細を調べる PostgreSQL練習問題SQL・設計・更新を総復習する おすすめ書籍PostgreSQL徹底入門を読む

PostgreSQL入門の講座一覧