[init.sql] PostgreSQLの初期化スクリプトでハマった話🐘

問題発生

Docker で PostgreSQL コンテナを立ち上げるときの初期化スクリプトで、こんなエラーに遭遇しました

ERROR: CREATE DATABASE cannot be executed from a function

原因

CREATE DATABASE を DO ブロック（PL/pgSQL）の中で実行しようとしたのが原因でした

-- ❌ これはNG
DO
$do$
BEGIN
   IF NOT EXISTS (SELECT FROM pg_catalog.pg_database WHERE datname = 'mydb') THEN
      CREATE DATABASE mydb;  -- ここでエラー
   END IF;
END
$do$;

PostgreSQL では CREATE DATABASE をトランザクションブロックや関数の中で実行できない制約があります

解決策

psql の \gexec メタコマンドを使って条件付きで実行するようにしました

-- ✅ これでOK
SELECT 'CREATE DATABASE mydb'
WHERE NOT EXISTS (
    SELECT FROM pg_database WHERE datname = 'mydb'
)\gexec

\gexec は psql のメタコマンドで、SELECT の結果を SQL コマンドとして実行してくれます。データベースが存在しない場合だけ CREATE DATABASE mydb という文字列が返されて実行される仕組みです

完成形

-- データベース作成（存在しない場合のみ）
SELECT 'CREATE DATABASE mydb'
WHERE NOT EXISTS (
    SELECT FROM pg_database WHERE datname = 'mydb'
)\gexec

-- ユーザー作成（存在しない場合のみ）
DO
$do$
BEGIN
   IF NOT EXISTS (
      SELECT FROM pg_catalog.pg_roles WHERE rolname = 'myuser'
   ) THEN
      CREATE USER myuser WITH PASSWORD 'mypassword';
   END IF;
END
$do$;

-- 権限付与
GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;

ユーザー作成は DO ブロックでも問題なく動きます。CREATE DATABASE だけが特殊なので注意が必要です

重要な注意点

\gexec は psql 専用

\gexec は psql クライアント専用のメタコマンドです。Docker の PostgreSQL イメージは初期化スクリプトを psql 経由で実行するので動きますが、他のクライアント（pgAdmin、DBeaver など）では使えません

もっとシンプルな方法もある

Docker なら環境変数で DB を作成する方がシンプルです・・・

services:
  db:
    image: postgres:latest
    environment:
      POSTGRES_DB: mydb
      POSTGRES_USER: myuser
      POSTGRES_PASSWORD: mypassword

これで自動的にデータベースとユーザーが作成されます

初期化スクリプトは一度だけ実行される

Docker の初期化スクリプトは空のデータディレクトリでのみ実行されるので、既存のボリュームがあると無視されます。完全に初期化し直したい場合は docker-compose down -v でボリュームごと削除する必要があります

まとめ

• CREATE DATABASE は関数やトランザクションブロックの中では実行できない
• 条件付きで実行したい場合は \gexec を使うのがスマート
• ただし \gexec は psql 専用なので注意
• Docker なら環境変数で DB を作る方がシンプルでおすすめ