PostgreSQL を業務で本格的に使い始めて10年。MySQL時代から合わせるともう20年近くDB触ってますが、PostgreSQLはハマるポイントが多い一方で、うまく付き合うと非常に強力なDBMSです。
この記事では、MySQLに慣れたエンジニアがPostgreSQLを使いこなすための最短ルートを、サンプルとともにまとめておきます。特に、**スキーマ、接続文字列、そしてRLS（行レベルセキュリティ）**あたりは、最初に抑えておくと後がラクです。
🔰 基本の違いとよく使うコマンド比較
| 操作 | MySQL | PostgreSQL |
|---|---|---|
| CLI起動 | mysql -u user -p |
psql -U user -d dbname |
| DB一覧 | SHOW DATABASES; |
\l |
| テーブル一覧 | SHOW TABLES; |
\dt |
| テーブル構造確認 | DESC users; |
\d users |
| DB切り替え | USE dbname; |
\c dbname |
PostgreSQLのpsqlは \ から始まる独自コマンドが充実してます。TAB 補完も強力。
📁 PostgreSQLの「スキーマ」概念に注意
MySQLはデータベースを分けるのが主流ですが、PostgreSQLは1つのデータベース内に複数のスキーマを持つ設計です。
✅ 例
CREATE SCHEMA accounting;
CREATE TABLE accounting.invoices (
id SERIAL PRIMARY KEY,
amount NUMERIC,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
デフォルトは public スキーマ。何も指定しないと public にテーブルが作られます。
🔐 public スキーマの扱いに注意
初期状態では「誰でもCREATEできてしまう」
GRANT ALL ON SCHEMA public TO PUBLIC;
つまりアプリユーザーが勝手にテーブルや関数を作れる可能性あり。以下で制限推奨。
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
🔌 接続文字列（DSN）と環境変数
PostgreSQLはURL形式で接続できます。
psql postgresql://user:pass@localhost:5432/mydb
または環境変数で設定：
export PGUSER=myuser
export PGPASSWORD=mypassword
export PGDATABASE=mydb
psql
CLIツールやアプリケーション設定ファイルに使えるので便利。
🔧 型の違い・対応表
| MySQL | PostgreSQL |
|---|---|
| INT AUTO_INCREMENT | SERIAL or IDENTITY |
| DATETIME | TIMESTAMP |
| BOOLEAN（TINYINT） | BOOLEAN |
| TEXT | TEXT |
🔄 REPLACE INTOは使えない → ON CONFLICT
MySQLの REPLACE INTO はPostgreSQLにはありません。代替はこれ：
INSERT INTO users (id, name)
VALUES (1, 'Hanako')
ON CONFLICT (id)
DO UPDATE SET name = EXCLUDED.name;
追記：MERGE文の追加（PostgreSQL 15新機能）
例（PostgreSQL 15以降）:
MERGE INTO users AS target
USING (VALUES (1, 'Hanako')) AS source(id, name)
ON target.id = source.id
WHEN MATCHED THEN
UPDATE SET name = source.name
WHEN NOT MATCHED THEN
INSERT (id, name) VALUES (source.id, source.name);
🧪 トランザクションの使い方
BEGIN;
UPDATE users SET name = 'foo' WHERE id = 1;
COMMIT; -- or ROLLBACK;
PostgreSQLはトランザクション制御がとても堅牢。
🛡️ Row-Level Security（RLS）を使いこなす
🔍 概要
- テーブルの行単位でアクセス制御ができる機能
- ユーザーごとに**「見える行」「操作できる行」**を制御可能
- SaaSやマルチテナントで非常に重宝する
✅ 基本構成
ALTER TABLE notes ENABLE ROW LEVEL SECURITY;
CREATE POLICY my_policy
ON notes
FOR SELECT
USING (user_id = current_setting('app.current_user_id')::INT);
ALTER TABLE notes FORCE ROW LEVEL SECURITY;
アプリケーション側でセッション変数を設定：
SELECT set_config('app.current_user_id', '101', false);
SELECT * FROM notes; -- user_id = 101 の行のみ見える
INSERT/UPDATE/DELETE制御も追加可能
CREATE POLICY write_policy
ON notes
FOR INSERT, UPDATE, DELETE
USING (user_id = current_setting('app.current_user_id')::INT)
WITH CHECK (user_id = current_setting('app.current_user_id')::INT);
🧭 search_path を活用したスキーマ分離
SET search_path TO accounting, public;
スキーマを切り替えて参照できる。アプリユーザーごとに設定して、名前衝突や誤操作を防ぐ。
ALTER ROLE app_user SET search_path = accounting;
✅ 実運用でのおすすめベストプラクティス
| 項目 | 推奨 |
|---|---|
| スキーマ設計 |
publicを使わず、機能ごとに独自スキーマを作成 |
| public制御 |
REVOKE CREATEで制限必須 |
| search_path | ユーザー/ロール単位で明示的に設定 |
| RLS | セッション変数（例: tenant_id, user_id）＋ポリシーで制御 |
| 接続文字列 | DSN形式 or 環境変数で柔軟に管理 |
| AUTO_INCREMENT代替 |
SERIAL or GENERATED AS IDENTITY
|
📝 最後に
PostgreSQLはMySQLよりも柔軟で強力な一方、設計ミスや運用ミスがセキュリティリスクに直結しやすいです。特にスキーマとRLSを理解して使えるようになると、かなり運用の質が上がります。
RLSをちゃんと組み込んだSaaSアプリは、アプリがバグってもテナント越境しない構造にできる。これはMySQLにはない大きな強みです。