接続

コマンドライン

psql -U postgres
psql -h localhost -p 5432 -U user -d mydb
psql "postgresql://user:pass@host:5432/mydb"

psqlメタコマンド

\l	データベース一覧を表示
\c dbname	データベースに接続
\dt	テーブル一覧を表示
\d tablename	テーブル構造を表示
\dn	スキーマ一覧を表示
\du	ロール一覧を表示
\q	psqlを終了
\i file.sql	SQLファイルを実行

テーブルとスキーマ

テーブルの作成

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email TEXT UNIQUE,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

スキーマ操作

CREATE SCHEMA app;
CREATE TABLE app.users (id SERIAL PRIMARY KEY);
SET search_path TO app, public;
DROP SCHEMA app CASCADE;

テーブルの変更

ALTER TABLE users ADD COLUMN age INT;
ALTER TABLE users ALTER COLUMN name TYPE TEXT;
ALTER TABLE users DROP COLUMN age;
ALTER TABLE users RENAME TO customers;

データ型

数値

INTEGER / INT	4バイト整数
BIGINT	8バイト整数
SERIAL	自動インクリメント整数
NUMERIC(p,s)	固定小数点数（例：NUMERIC(10,2)）
REAL / DOUBLE PRECISION	浮動小数点数（4 / 8バイト）
BOOLEAN	true / false / null

文字列とバイナリ

TEXT	可変長の無制限テキスト
VARCHAR(n)	n文字までの可変長テキスト
CHAR(n)	固定長テキスト
BYTEA	バイナリデータ
UUID	128ビットの汎用一意識別子

日付、JSONと配列

DATE	カレンダー日付
TIMESTAMPTZ	タイムゾーン付きタイムスタンプ
INTERVAL	時間間隔（例：'2 days'）
JSONB	バイナリJSON（インデックス可能）
INT[] / TEXT[]	配列型

クエリ

挿入

INSERT INTO users (name, email) VALUES ('Alice', '[email protected]') RETURNING id; INSERT INTO users (name, email) VALUES ('Bob', '[email protected]'), ('Carol', '[email protected]');

選択

SELECT * FROM users WHERE id = 1;
SELECT name, email FROM users
ORDER BY name LIMIT 10 OFFSET 20;

更新

UPDATE users SET email = '[email protected]' WHERE id = 1 RETURNING *;

アップサート

INSERT INTO users (email, name) VALUES ('[email protected]', 'Alice') ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;

削除

DELETE FROM users WHERE id = 1 RETURNING *;
TRUNCATE TABLE users RESTART IDENTITY;

ジョインとサブクエリ

ジョインの種類

INNER JOIN	両テーブルで一致する行
LEFT JOIN	左の全行 + 一致する右の行
RIGHT JOIN	右の全行 + 一致する左の行
FULL OUTER JOIN	両テーブルの全行
CROSS JOIN	直積（デカルト積）
LATERAL JOIN	外部行を参照するサブクエリ

CTE（共通テーブル式）

WITH active AS (
  SELECT * FROM users WHERE active = true
)
SELECT a.name, o.total
FROM active a
JOIN orders o ON a.id = o.user_id;

サブクエリ

SELECT name FROM users
WHERE id IN (
  SELECT user_id FROM orders
  WHERE total > 100
);

インデックス

作成と削除

CREATE INDEX idx_name ON users(name);
CREATE UNIQUE INDEX idx_email ON users(email);
CREATE INDEX idx_gin ON posts USING GIN(tags);
DROP INDEX idx_name;

インデックスの種類

B-tree	デフォルト、=、<、>、BETWEENに有効
Hash	等価比較のみ
GIN	汎用転置インデックス — 配列、JSONB、全文検索
GiST	汎用検索ツリー — 幾何、範囲
BRIN	ブロック範囲 — ソート済みの大きなテーブル

クエリ分析

EXPLAIN ANALYZE
SELECT * FROM users WHERE name = 'Alice';

関数とプロシージャ

SQL関数

CREATE FUNCTION active_count()
RETURNS INTEGER AS $$
  SELECT COUNT(*)::INT FROM users
  WHERE active = true;
$$ LANGUAGE sql;
SELECT active_count();

PL/pgSQL関数

CREATE FUNCTION greet(name TEXT)
RETURNS TEXT AS $$
BEGIN
  RETURN 'Hello, ' || name;
END;
$$ LANGUAGE plpgsql;

便利な組み込み関数

NOW() / CURRENT_TIMESTAMP	タイムゾーン付きの現在のタイムスタンプ
AGE(ts1, ts2)	タイムスタンプ間の間隔
COALESCE(a, b)	最初のnullでない値
NULLIF(a, b)	a = bの場合はNULL
GENERATE_SERIES(1,10)	連続値の行を生成
STRING_AGG(col, ',')	区切り文字で値を結合

ロールと権限

ロールの管理

CREATE ROLE app LOGIN PASSWORD 'secret';
ALTER ROLE app SET search_path TO myapp;
DROP ROLE app;

権限の付与

GRANT ALL ON DATABASE mydb TO app;
GRANT SELECT, INSERT ON users TO reader;
GRANT USAGE ON SCHEMA public TO app;
REVOKE INSERT ON users FROM reader;

行レベルセキュリティ

ALTER TABLE users ENABLE ROW LEVEL SECURITY;
CREATE POLICY user_own ON users
FOR ALL USING (id = current_setting('app.uid')::INT);

JSONサポート

JSONB演算子

->'key'	キーでJSON値を取得（JSONとして）
->>'key'	キーでJSON値を取得（テキストとして）
#>'{a,b}'	パスでネストした値を取得
@>	包含（左が右を含む）
?	キーが存在する
\|\|	JSONB値を結合

JSONBクエリ

SELECT data->>'name' FROM profiles
WHERE data @> '{"active": true}';

SELECT * FROM profiles
WHERE data ? 'email';

JSONB関数

SELECT jsonb_each(data) FROM profiles;
SELECT jsonb_array_elements('[1,2,3]');
SELECT jsonb_set(data, '{name}', '"Alice"')
FROM profiles WHERE id = 1;

よくあるパターン

トランザクション

BEGIN;
UPDATE accounts SET balance = balance - 100
WHERE id = 1;
UPDATE accounts SET balance = balance + 100
WHERE id = 2;
COMMIT;  -- or ROLLBACK;

ウィンドウ関数

SELECT name, salary,
  RANK() OVER (ORDER BY salary DESC),
  AVG(salary) OVER (PARTITION BY dept)
FROM employees;

データのコピー

COPY users TO '/tmp/users.csv'
WITH (FORMAT csv, HEADER);
COPY users FROM '/tmp/users.csv'
WITH (FORMAT csv, HEADER);

pg_dumpバックアップ

pg_dump -U postgres mydb > backup.sql
pg_dump -Fc mydb > backup.dump
pg_restore -d mydb backup.dump

POSTGRESQL クイックリファレンス