PostgreSQLとは
PostgreSQL（ポストグレスキューエル）は、拡張性と標準SQL準拠に優れたオープンソースのリレーショナルデータベース管理システム（RDBMS）です。
主な特徴
- 30年以上の開発実績を持つ安定性
- ACID準拠のトランザクション処理
- JSON、配列などの高度なデータ型
- テーブル継承やマテリアライズドビュー
- コミュニティ主導の活発な開発
PostgreSQLは「ポスグレ」や「ポスグレSQL」と短縮して呼ばれることもあります。正式な発音は「ポストグレス・キュー・エル」です。
無料
オープンソース
クロスプラットフォーム
エンタープライズ対応
インストールと設定
主要OSでのインストール
# Ubuntu/Debian
$ sudo apt update
$ sudo apt install postgresql postgresql-contrib
# macOS (Homebrew)
$ brew install postgresql
# Windows
# インストーラーをダウンロードして実行
# https://www.postgresql.org/download/windows/
基本的な設定
PostgreSQLはpostgresql.confとpg_hba.confの2つの主要設定ファイルを使用します。
- postgresql.conf: メモリ、接続数などのデータベース設定
- pg_hba.conf: クライアント認証ポリシーの設定
データベース操作
基本コマンド
# PostgreSQLサービスの開始/停止
$ sudo systemctl start postgresql
$ sudo systemctl stop postgresql
# PostgreSQLに接続
$ psql -U ユーザー名 -d データベース名
# デフォルトのpostgresユーザーで接続
$ sudo -u postgres psql
データベース管理
-- データベース一覧表示
\l または \list
-- 新規データベース作成
CREATE DATABASE データベース名;
-- データベース削除
DROP DATABASE データベース名;
-- データベース切り替え
\c データベース名
初めてPostgreSQLをインストールした場合、postgresという名前の管理者ユーザーとデータベースが自動的に作成されています。
テーブル操作
テーブル作成
CREATE TABLE 社員 (
社員ID SERIAL PRIMARY KEY,
氏名 VARCHAR(100) NOT NULL,
メール VARCHAR(255) UNIQUE,
部署ID INTEGER REFERENCES 部署(部署ID),
入社日 DATE,
給与 NUMERIC(10,2)
);
テーブル操作コマンド
-- テーブル一覧表示
\dt
-- テーブル構造表示
\d テーブル名
-- テーブル変更
ALTER TABLE テーブル名 ADD COLUMN 列名 データ型;
ALTER TABLE テーブル名 DROP COLUMN 列名;
ALTER TABLE テーブル名 RENAME TO 新テーブル名;
-- テーブル削除
DROP TABLE テーブル名;
データ操作 (CRUD)
CREATE - データ挿入
-- 単一行挿入
INSERT INTO 社員 (氏名, メール, 部署ID, 入社日, 給与)
VALUES ('山田太郎', 'yamada@example.com', 1, '2022-04-01', 350000);
-- 複数行挿入
INSERT INTO 社員 (氏名, メール, 部署ID)
VALUES
('佐藤花子', 'sato@example.com', 2),
('鈴木一郎', 'suzuki@example.com', 1);
READ - データ取得
-- 全件取得
SELECT * FROM 社員;
-- 条件付き取得
SELECT 社員ID, 氏名, 給与 FROM 社員
WHERE 部署ID = 1
ORDER BY 給与 DESC
LIMIT 10;
-- 集計関数
SELECT 部署ID, COUNT(*), AVG(給与) FROM 社員
GROUP BY 部署ID
HAVING COUNT(*) > 5;
UPDATE - データ更新
UPDATE 社員
SET 給与 = 給与 * 1.1
WHERE 部署ID = 2;
DELETE - データ削除
DELETE FROM 社員 WHERE 入社日 < '2010-01-01';
主要データ型
| カテゴリ |
データ型 |
説明 |
| 数値型 |
INTEGER |
通常の整数 (-2147483648 〜 2147483647) |
| BIGINT |
大きな整数 (8バイト) |
| NUMERIC(p,s) |
精度pと小数点以下桁数sの10進数 |
| 文字型 |
VARCHAR(n) |
可変長文字列 (最大n文字) |
| TEXT |
無制限長のテキスト |
| 日付/時刻型 |
DATE |
日付 (年, 月, 日) |
| TIME |
時刻 (時, 分, 秒) |
| TIMESTAMP |
日付と時刻 |
| 論理型 |
BOOLEAN |
真/偽 |
| ENUM |
列挙型 (独自定義) |
| 特殊型 |
UUID |
汎用一意識別子 |
| JSON/JSONB |
JSON形式データ |
| ARRAY |
配列型 |
PostgreSQLのJSONB型はJSONの内部表現を最適化して高速な検索を実現します。JSON型よりもJSONB型の使用が推奨されています。
制約
主要な制約
- PRIMARY KEY: 主キー制約 (一意性+NOT NULL)
- FOREIGN KEY: 外部キー制約 (参照整合性)
- UNIQUE: 一意性制約
- NOT NULL: NULL値禁止
- CHECK: 値のチェック条件
- DEFAULT: デフォルト値
制約の使用例
CREATE TABLE 商品 (
商品ID UUID DEFAULT gen_random_uuid() PRIMARY KEY,
商品名 VARCHAR(100) NOT NULL,
価格 NUMERIC(10,2) CHECK (価格 > 0),
カテゴリID INTEGER REFERENCES カテゴリ(カテゴリID),
SKUコード VARCHAR(50) UNIQUE,
登録日 TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
在庫数 INTEGER DEFAULT 0 NOT NULL
);
外部キー制約のオプション
FOREIGN KEY (カテゴリID) REFERENCES カテゴリ(カテゴリID)
ON DELETE CASCADE -- 親レコード削除時に子も削除
ON UPDATE CASCADE -- 親キー更新時に子も更新
高度な機能
インデックス
-- B-treeインデックス (標準)
CREATE INDEX idx_社員_氏名 ON 社員(氏名);
-- 複合インデックス
CREATE INDEX idx_社員_部署_入社日 ON 社員(部署ID, 入社日);
-- 部分インデックス
CREATE INDEX idx_高額給与社員 ON 社員(給与)
WHERE 給与 > 500000;
トランザクション
BEGIN;
UPDATE 口座 SET 残高 = 残高 - 10000 WHERE 口座番号 = '1001';
UPDATE 口座 SET 残高 = 残高 + 10000 WHERE 口座番号 = '1002';
COMMIT; -- または ROLLBACK;
ビューとマテリアライズドビュー
-- 通常ビュー (仮想テーブル)
CREATE VIEW 部署別給与概要 AS
SELECT 部署.部署名, COUNT(*) AS 社員数,
AVG(社員.給与) AS 平均給与
FROM 社員
JOIN 部署 ON 社員.部署ID = 部署.部署ID
GROUP BY 部署.部署名;
よく使うコマンド
psqlコマンド (メタコマンド)
- \?: psqlコマンドのヘルプ
- \h: SQLコマンドのヘルプ
- \l: データベース一覧
- \c DB名: データベース接続
- \d: テーブル一覧
- \d テーブル名: テーブル定義確認
- \du: ユーザー一覧
- \q: psql終了
バックアップと復元
# データベースのバックアップ
$ pg_dump -U ユーザー名 -F c -b -v -f backup.dump データベース名
# データベースの復元
$ pg_restore -U ユーザー名 -d データベース名 backup.dump