PostgreSQL: PostgreSQLデータベースの作成と管理

-- 環境ごとに異なるロケールでデータベースを作成
CREATE DATABASE shop_dev
    WITH ENCODING = 'UTF8'
    LC_COLLATE = 'en_US.utf8'
    LC_CTYPE = 'en_US.utf8'
    TEMPLATE = template1;

CREATE DATABASE shop_staging
    WITH ENCODING = 'UTF8'
    LC_COLLATE = 'en_US.utf8'
    LC_CTYPE = 'en_US.utf8';

CREATE DATABASE shop_prod
    WITH ENCODING = 'UTF8'
    LC_COLLATE = 'en_US.utf8'
    LC_CTYPE = 'en_US.utf8';

CREATE DATABASE name
    [WITH]
    [OWNER = user_name]
    [TEMPLATE = template]
    [ENCODING = encoding_name]
    [LC_COLLATE = lc_collate]
    [LC_CTYPE = lc_ctype]
    [TABLESPACE = tablespace_name]
    [CONNECTION LIMIT = conn_limit]
    [IS_TEMPLATE = true | false];

-- デフォルト設定でシンプルなデータベースを作成
CREATE DATABASE my_app;

-- 作成を確認
\l

CREATE TABLE

-- すべての一般的なオプションを指定してデータベースを作成
CREATE DATABASE shop_prod
    WITH OWNER = devuser
    ENCODING = 'UTF8'
    LC_COLLATE = 'en_US.utf8'
    LC_CTYPE = 'en_US.utf8'
    CONNECTION LIMIT = 100;

CREATE TABLE

-- ステップ1: template1に接続して共通の拡張を追加
\c template1

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pg_trgm";

-- ステップ2: これで新しいデータベースはすべ��これらの拡張を持つ
\c postgres

CREATE DATABASE new_project;
-- new_projectは自動的にuuid-osspとpg_trgmを持つ

-- ステップ3: 確認
\c new_project
\dx

CREATE TABLE

-- template1が必要なエンコーディングと異なる場合、
-- template0を使用して特定のエンコーディングでデータベースを作成
CREATE DATABASE db_with_latin
    TEMPLATE = template0
    ENCODING = 'LATIN1'
    LC_COLLATE = 'C'
    LC_CTYPE = 'C';

CREATE TABLE

-- データベース名の変更（先に全ユーザーを切断する必要あり）
ALTER DATABASE shop_dev RENAME TO shop_development;

-- データベースのデフォルトsearch_pathを設定
ALTER DATABASE shop_prod SET search_path TO public, admin;

-- 特定のデータベースにwork_memを設定
ALTER DATABASE shop_prod SET work_mem = '64MB';

-- 接続制限の変更
ALTER DATABASE shop_prod CONNECTION LIMIT = 200;

-- テンプレートとしてマーク
ALTER DATABASE shop_base IS_TEMPLATE = true;

-- SQL文が正常に実行されました

-- データベースへのアクティブな接続を検索
SELECT pid, usename, application_name, client_addr, state
FROM pg_stat_activity
WHERE datname = 'shop_dev';

DROP DATABASE [IF EXISTS] name [WITH (FORCE)];

-- 安全な削除（データベースが存在しなくてもエラーにしない）
DROP DATABASE IF EXISTS old_project;

-- 強制削除（先に全ユーザーを切断、PG 13+）
DROP DATABASE IF EXISTS shop_dev WITH (FORCE);

-- SQL文が正常に実行されました

-- 利用可能なエンコーディングを一覧表示
SELECT name, description FROM pg_encodings LIMIT 10;

-- 利用可能な照合順序を一覧表示
SELECT name, locale FROM pg_collation LIMIT 10;

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

-- テストテーブルにテキストデータを作成
CREATE TABLE sort_test (name TEXT);
INSERT INTO sort_test VALUES ('apple'), ('Banana'), ('cherry'), ('Apple');

-- Cロケールでソート（バイト順：大文字が先）
SELECT name FROM sort_test ORDER BY name COLLATE "C";

-- en_US.utf8でソート（辞書順：同じ文字内で大文字小文字を区別しない）
SELECT name FROM sort_test ORDER BY name COLLATE "en_US.utf8";

-- クリーンアップ
DROP TABLE sort_test;

-- Cロケール:
  Apple
  Banana
  apple
  cherry

-- en_US.utf8:
  apple
  Apple
  Banana
  cherry

# 全データベースを一覧表示
\l

# shop_devデータベースに切り替え
\c shop_dev

# 現在の接続を確認
\conninfo

# postgresデータベースに戻る
\c postgres

# コマンドが正常に実行されました

-- ============================================
-- 完全な例：Eコマースプロジェクト用に
-- dev/staging/prodデータベースをセットアップ
-- ============================================

-- ステップ1: postgresに接続（メンテナンス用データベース）
\c postgres

-- ステップ2: 英語ロケールでdevデータベースを作成
CREATE DATABASE shop_dev
    WITH ENCODING = 'UTF8'
    LC_COLLATE = 'en_US.utf8'
    LC_CTYPE = 'en_US.utf8'
    OWNER = devuser
    CONNECTION LIMIT = 50;

-- ステップ3: stagingデータベースを作成（prodと同じ設定）
CREATE DATABASE shop_staging
    WITH ENCODING = 'UTF8'
    LC_COLLATE = 'en_US.utf8'
    LC_CTYPE = 'en_US.utf8'
    OWNER = devuser
    CONNECTION LIMIT = 50;

-- ステップ4: 接続制限付きでprodデータベースを作成
CREATE DATABASE shop_prod
    WITH ENCODING = 'UTF8'
    LC_COLLATE = 'en_US.utf8'
    LC_CTYPE = 'en_US.utf8'
    OWNER = devuser
    CONNECTION LIMIT = 200;

-- ステップ5: prodにデフォルトsearch_pathを設定
ALTER DATABASE shop_prod SET search_path TO public, admin;

-- ステップ6: すべてのデータベー��の作成を確認
SELECT datname, pg_encoding_to_char(encoding) AS encoding,
       datcollate, datctype, datconnlimit
FROM pg_database
WHERE datname LIKE 'shop_%'
ORDER BY datname;

   datname    | encoding |  datcollate  |   datctype   | datconnlimit
--------------+----------+--------------+--------------+-------------
 shop_dev     | UTF8     | en_US.utf8   | en_US.utf8   |           50
 shop_prod    | UTF8     | en_US.utf8   | en_US.utf8   |          200
 shop_staging | UTF8     | en_US.utf8   | en_US.utf8   |           50