# 基本接続
psql -h ホスト名 -p ポート番号 -U ユーザー名 -d データベース名

# ローカル接続
psql -U postgres
psql -d mydb

# 特定のホストに接続
psql -h localhost -U myuser -d mydb

# パスワード入力を求める
psql -U myuser -d mydb -W

# 接続文字列で接続
psql "postgresql://username:password@host:port/database"

-- ヘルプ表示
\?

-- 現在の接続情報表示
\conninfo

-- データベース切り替え
\c データベース名

-- 終了
\q

-- データベース一覧
\l
\list

-- データベース作成
CREATE DATABASE database_name;
CREATE DATABASE mydb WITH OWNER myuser ENCODING 'UTF8';

-- データベース削除
DROP DATABASE database_name;

-- データベース情報表示
\l database_name

-- テーブル一覧
\dt
\dt+  -- 詳細情報付き

-- 特定のスキーマのテーブル
\dt schema_name.*

-- テーブル構造表示
\d table_name
\d+ table_name  -- より詳細

-- カラム情報
\d+ table_name

-- テーブル作成
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- カラム追加
ALTER TABLE users ADD COLUMN age INTEGER;

-- カラム変更
ALTER TABLE users ALTER COLUMN age TYPE SMALLINT;

-- カラム削除
ALTER TABLE users DROP COLUMN age;

-- テーブル名変更
ALTER TABLE users RENAME TO customers;

-- テーブル削除
DROP TABLE table_name;

-- データ挿入
INSERT INTO users (name, email) VALUES ('John Doe', 'john@example.com');

-- 複数行挿入
INSERT INTO users (name, email) VALUES
    ('Alice', 'alice@example.com'),
    ('Bob', 'bob@example.com');

-- データ更新
UPDATE users SET email = 'newemail@example.com' WHERE id = 1;

-- データ削除
DELETE FROM users WHERE id = 1;

-- 全データ削除
TRUNCATE TABLE users;

-- 基本検索
SELECT * FROM users;
SELECT name, email FROM users;

-- 条件付き検索
SELECT * FROM users WHERE age > 25;
SELECT * FROM users WHERE name LIKE 'J%';

-- ソート
SELECT * FROM users ORDER BY created_at DESC;

-- 制限
SELECT * FROM users LIMIT 10 OFFSET 20;

-- グループ化
SELECT COUNT(*), age FROM users GROUP BY age;

-- インデックス一覧
\di

-- インデックス作成
CREATE INDEX idx_users_email ON users(email);
CREATE UNIQUE INDEX idx_users_username ON users(username);

-- 複合インデックス
CREATE INDEX idx_users_name_age ON users(name, age);

-- インデックス削除
DROP INDEX idx_users_email;

-- インデックス使用状況確認
SELECT * FROM pg_stat_user_indexes;

-- ユーザー一覧
\du

-- ユーザー作成
CREATE USER username WITH PASSWORD 'password';
CREATE ROLE username WITH LOGIN PASSWORD 'password';

-- ユーザー削除
DROP USER username;

-- パスワード変更
ALTER USER username WITH PASSWORD 'newpassword';

-- 権限付与
GRANT ALL PRIVILEGES ON DATABASE mydb TO username;
GRANT SELECT, INSERT ON users TO username;

-- 権限剥奪
REVOKE ALL ON DATABASE mydb FROM username;

-- スキーマ一覧
\dn

-- スキーマ作成
CREATE SCHEMA schema_name;

-- スキーマ削除
DROP SCHEMA schema_name CASCADE;

-- 現在のスキーマ確認
SHOW search_path;

-- スキーマパス設定
SET search_path TO schema_name, public;

# データベース全体をバックアップ
pg_dump -U username -h hostname database_name > backup.sql

# 圧縮バックアップ
pg_dump -U username -h hostname -F c database_name > backup.dump

# 特定のテーブルのみ
pg_dump -U username -t table_name database_name > table_backup.sql

# スキーマのみ
pg_dump -U username -s database_name > schema_only.sql

# データのみ
pg_dump -U username -a database_name > data_only.sql

# SQLファイルからリストア
psql -U username -d database_name < backup.sql

# カスタムフォーマットからリストア
pg_restore -U username -d database_name backup.dump

# 新しいデータベースを作成してリストア
createdb -U username new_database
pg_restore -U username -d new_database backup.dump

-- データベースサイズ
SELECT pg_size_pretty(pg_database_size('database_name'));

-- テーブルサイズ
SELECT pg_size_pretty(pg_total_relation_size('table_name'));

-- 現在の接続数
SELECT count(*) FROM pg_stat_activity;

-- アクティブなクエリ
SELECT pid, now() - pg_stat_activity.query_start AS duration, query
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes';

-- バージョン確認
SELECT version();

-- 設定確認
SHOW all;
SHOW shared_preload_libraries;

-- テーブル統計
SELECT * FROM pg_stat_user_tables;

-- インデックス統計
SELECT * FROM pg_stat_user_indexes;

-- 実行計画
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';

-- 時間表示
\timing

-- 拡張表示モード
\x

-- ページャー無効化
\pset pager off

-- 出力フォーマット変更
\pset format csv
\pset format html

-- ファイルに出力
\o output.txt
SELECT * FROM users;
\o

-- SQLファイル実行
\i script.sql

-- 変数設定・使用
\set myvar 'value'
SELECT :'myvar';

-- 全設定表示
\pset

-- エラー時停止設定
\set ON_ERROR_STOP on

-- 行数表示
\pset footer off

-- 文字列連結
SELECT name || ' - ' || email FROM users;

-- 文字列検索
SELECT * FROM users WHERE name ILIKE '%john%';

-- 文字列置換
UPDATE users SET email = REPLACE(email, 'old.com', 'new.com');

-- 現在日時
SELECT NOW(), CURRENT_DATE, CURRENT_TIME;

-- 日付フォーマット
SELECT TO_CHAR(created_at, 'YYYY-MM-DD HH24:MI:SS') FROM users;

-- 日付計算
SELECT * FROM users WHERE created_at > NOW() - INTERVAL '1 day';

-- JSON検索
SELECT * FROM users WHERE data->>'key' = 'value';

-- JSON更新
UPDATE users SET data = jsonb_set(data, '{key}', '"new_value"');

-- ロック確認
SELECT * FROM pg_locks WHERE NOT granted;

-- 実行中のクエリ確認
SELECT pid, state, query FROM pg_stat_activity WHERE state = 'active';

-- クエリ強制終了
SELECT pg_terminate_backend(pid);

-- 統計情報更新
ANALYZE;
ANALYZE table_name;

-- VACUUM実行
VACUUM;
VACUUM FULL table_name;

-- 遅いクエリ確認
SELECT query, calls, total_time, mean_time
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

-- キャッシュヒット率
SELECT
    sum(heap_blks_read) as heap_read,
    sum(heap_blks_hit)  as heap_hit,
    sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as ratio
FROM pg_statio_user_tables;