はじめに
psql による接続
PostgreSQL のコマンドラインクライアントが、psql です。バックスラッシュ(\)で始まるのが、メタコマンドです。データベースの一覧には \l を、テーブルの記述には \d を、ヘルプには \? を使うしましょう。ほとんどのドライバがサポートし便利なのが、接続文字列(postgresql://)です。SQL ファイルの実行には、\i filename.sql を使います。パスワードのタイプを避けるには、PGPASSWORD env 変数か ~/.pgpass ファイルを設定しましょう。
# connect to a database
psql -h localhost -p 5432 -U postgres -d mydb
# -h host, -p port, -U user, -d database
# connect via connection string
psql "postgresql://user:pass@localhost:5432/mydb"
# common psql meta-commands
\l # list databases
\c mydb # connect to mydb
\dt # list tables
\d users # describe table
\dn # list schemas
\df # list functions
\du # list roles/users
\q # quit
\? # help on meta-commands
\timing # toggle query timingデータベースの作成と削除
新しいデータベースを作るのが、CREATE DATABASE です。owner、encoding、locale、template を指定できます。ロケール固有のデータのないきれいなテンプレートが、template0 で、カスタマイズ可能なのが、template1(デフォルト)です。アクティブな接続がないことを要求するのが、DROP DATABASE です — (13+ の)WITH (FORCE) が先に切断します。自分が接続しているデータベースは落とせません。名前を更新するが、ディスク上のディレクトリを更新しないのが、ALTER DATABASE RENAME です。
# create a database
CREATE DATABASE mydb
WITH OWNER alice
ENCODING 'UTF8'
LC_COLLATE 'en_US.UTF-8'
LC_CTYPE 'en_US.UTF-8'
TEMPLATE template0
CONNECTION LIMIT 100;
# create from a template (clone)
CREATE DATABASE mydb_copy TEMPLATE mydb;
# drop a database (must disconnect first)
DROP DATABASE IF EXISTS mydb;
# drop with force (PostgreSQL 13+)
DROP DATABASE IF EXISTS mydb WITH (FORCE);
# rename a database
ALTER DATABASE mydb RENAME TO newdb;スキーマと search_path
データベースオブジェクトのための名前空間が、スキーマです — テーブルのためのフォルダのような。デフォルトのスキーマが「public」です。修飾されていない名前を参照したとき、どのスキーマが検索されるかを決めるのが、search_path です。マルチテナントアプリの整理、関心の分離、バージョン管理にスキーマを使いましょう。依存オブジェクトを落とすのが CASCADE で、オブジェクトが存在すれば拒否するのが、RESTRICT(デフォルト)です。1 つの接続内のマルチテナンシーには、データベースよりスキーマの方が柔軟です。
# create a schema
CREATE SCHEMA IF NOT EXISTS app_schema AUTHORIZATION alice;
# set the search path (schema lookup order)
SET search_path TO app_schema, public;
SHOW search_path;
# create a table in a specific schema
CREATE TABLE app_schema.users (id serial PRIMARY KEY);
# move a table between schemas
ALTER TABLE public.users SET SCHEMA app_schema;
# list objects in a schema
SELECT * FROM information_schema.tables
WHERE table_schema = 'app_schema';
# drop a schema (and its objects)
DROP SCHEMA IF EXISTS app_schema CASCADE;設定
postgresql.conf に置かれるのが、PostgreSQL の設定です。現在値を読むのが SHOW で、セッションのために変えるのが SET、現在のトランザクションのためだけが SET LOCAL、永続するデフォルトを設定するのが、ALTER DATABASE/ROLE です。サーバーの再起動を必要とするのが、一部の設定(shared_buffers、max_connections)で、即座に適用されるのが、他(work_mem、statement_timeout)です。再起動なしで設定ファイルの変更を適用するのが、pg_reload_conf() です。遅いクエリのためには、log_min_duration_statement を監視しましょう。
# view a setting
SHOW shared_buffers;
SHOW max_connections;
# set a parameter for the session
SET work_mem = '64MB';
SET statement_timeout = '30s';
# set a parameter for a transaction
BEGIN;
SET LOCAL work_mem = '256MB';
-- heavy sort here
COMMIT; -- reverts to previous value
# set at database or role level (persists)
ALTER DATABASE mydb SET log_min_duration_statement = 100;
ALTER ROLE alice SET search_path TO app_schema, public;
# reload config without restart
SELECT pg_reload_conf();
# view config file location
SHOW config_file;psql のスクリプティングと変数
psql がスクリプティングをサポートするのは、変数(\set)、ファイルの取り込み(\i)、出力のリダイレクト(\o)、シェル実行(\!)です。:varname により代入されるのが、変数です。スクリプティングのループと条件分岐には、\set を使いましょう。ファイルを実行して exit するのが、-f フラグです。バッチジョブと移行に有用です。複雑なスクリプティングのためには、PL/pgSQL の関数か、移行管理のための sqitch や Flyway のような外部ツールを検討しましょう。
# run a SQL file
psql -d mydb -f script.sql
psql -d mydb < script.sql
# inside psql
\i /path/to/script.sql
# use psql variables
\set table_name 'users'
SELECT * FROM :table_name;
# prompt for input
\echo -n 'Enter user ID: ' \set user_id `read x && echo $x`
SELECT * FROM users WHERE id = :user_id;
# output to file
\o results.txt
SELECT * FROM users;
\o
# execute shell command
\! dateテーブルと制約
テーブルの作成
自動増分整数のためのシーケンスを自動作成するのが、serial です。新しいテーブルには、代わりに(SQL 標準であり PG10 以降推奨の)GENERATED ALWAYS AS IDENTITY を使いましょう。制約:PRIMARY KEY(一意 + 非 null)、UNIQUE、NOT NULL、CHECK、REFERENCES(外部キー)です。親が削除されたとき依存行を削除するのが、ON DELETE CASCADE で、FK を NULL に設定するのが、SET NULL です。セッション後に消えるのが、TEMP のテーブルです — 中間結果に最適です。
CREATE TABLE users (
id serial PRIMARY KEY,
username varchar(50) UNIQUE NOT NULL,
email varchar(255) UNIQUE NOT NULL,
password text NOT NULL,
age integer CHECK (age >= 0 AND age <= 150),
role varchar(20) NOT NULL DEFAULT 'user',
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now()
);
# create with a foreign key
CREATE TABLE posts (
id serial PRIMARY KEY,
user_id integer NOT NULL REFERENCES users(id) ON DELETE CASCADE,
title text NOT NULL,
body text,
published boolean DEFAULT false
);
# create a temporary table (session-scoped)
CREATE TEMP TABLE temp_stats AS
SELECT user_id, count(*) FROM posts GROUP BY user_id;Identity 列(PG10+)
serial の SQL 標準の代替が、GENERATED ALWAYS AS IDENTITY です。シーケンスを列に結び付けるため、列の削除がシーケンスを落とします(serial が孤立シーケンスを残します)。ID の手動挿入を防ぐのが、ALWAYS です(強制するには OVERRIDING SYSTEM VALUE を)。serial のように振る舞うのが、BY DEFAULT です(手動挿入を許します)。新しいテーブルには identity 列を使い、既存のものには serial で構いません。どちらも下ではシーケンスを使います。
# preferred over serial (SQL standard)
CREATE TABLE products (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL
);
# allow manual override (like serial behavior)
CREATE TABLE products (
id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
name text NOT NULL
);
# restart a sequence
ALTER TABLE products ALTER COLUMN id RESTART WITH 1000;
# view identity info
SELECT * FROM pg_sequences WHERE sequencename = 'products_id_seq';テーブルの変更
テーブルを再作成せずにスキーマを進化させるのが、ALTER TABLE です。デフォルト(非揮発)付きの列追加が、PG11 以降は速いです(フルのテーブルの書き換えなし)。変換のための USING 句と共に、フルの書き換えを必要とし得るのが、列タイプの変更です。依存オブジェクト(ビュー、外部キー)を落とすのが、CASCADE です — 慎重に使いましょう。将来の管理を容易にするため、名前付きの制約を加えましょう。スクリプトを冪等にするのが、IF EXISTS/IF NOT EXISTS です。
# add a column
ALTER TABLE users ADD COLUMN bio text;
ALTER TABLE users ADD COLUMN score numeric DEFAULT 0;
# drop a column
ALTER TABLE users DROP COLUMN IF EXISTS bio;
ALTER TABLE users DROP COLUMN bio CASCADE; -- drop dependent objects
# rename a column
ALTER TABLE users RENAME COLUMN username TO handle;
# change column type
ALTER TABLE users ALTER COLUMN age TYPE smallint USING age::smallint;
# set/drop default
ALTER TABLE users ALTER COLUMN role SET DEFAULT 'member';
ALTER TABLE users ALTER COLUMN role DROP DEFAULT;
# add constraint
ALTER TABLE users ADD CONSTRAINT email_check
CHECK (email ~ '^[^@]+@[^@]+\.[^@]+$');制約
データベースレベルでデータの整合性を強制するのが、制約です。アプリのチェックより必ずこれを推奨しましょう。中間テーブルに機能するのが、複合キーです。PK の変化を FK へ伝播させるのが、ON UPDATE CASCADE で、FK を null にするのが、ON DELETE SET NULL です(FK 列は nullable でなければなりません)。コミット時(ステートメントごとではなく)に確認するのが、DEFERRABLE の制約です。循環参照に有用です。管理しやすいよう制約に名前を付けましょう — 自動生成された名前が醜く、データベース間で異なります。
# named constraint for easy management
ALTER TABLE users
ADD CONSTRAINT positive_age CHECK (age >= 0);
# composite primary key
CREATE TABLE enrollments (
student_id integer REFERENCES students(id),
course_id integer REFERENCES courses(id),
PRIMARY KEY (student_id, course_id)
);
# unique constraint on multiple columns
ALTER TABLE users ADD CONSTRAINT unique_email_domain
UNIQUE (email, domain);
# foreign key with actions
ALTER TABLE posts
ADD FOREIGN KEY (user_id) REFERENCES users(id)
ON UPDATE CASCADE ON DELETE SET NULL;
# drop a constraint
ALTER TABLE users DROP CONSTRAINT positive_age;
# defer a constraint until commit
SET CONSTRAINTS unique_email_domain DEFERRED;テーブルの継承
PostgreSQL 固有の機能が、テーブルの継承です。親から列を継承するのが、子テーブルです。ONLY を使わない限り、親のクエリが、すべての子からの行を返します。テーブルのパーティショニング(宣言的パーティショニング以前)と分類のモデル化に有用です。しかし、継承には癖があります:外部キーが伝播せず、UNIQUE の制約がテーブルごとにしか適用されません。新しいパーティショニングの必要性には、(PARTITION BY の)宣言的パーティショニングを代わりに使いましょう — より堅牢でより良くサポートされています。
# PostgreSQL supports table inheritance
CREATE TABLE people (
id serial PRIMARY KEY,
name text NOT NULL
);
CREATE TABLE employees (
salary numeric,
dept text
) INHERITS (people);
CREATE TABLE customers (
credit_limit numeric
) INHERITS (people);
# query parent sees all children
SELECT * FROM people; -- includes employees and customers
SELECT * FROM ONLY people; -- only people, not children
# insert into a child
INSERT INTO employees (name, salary, dept)
VALUES ('Alice', 50000, 'Engineering');データ型
数値型
データにサイズが合う整数型を使いましょう — 小さい範囲には smallint、大きいものには bigint です。real/double より遅いが正確なのが(金銭には不可欠)、numeric/decimal です。金銭に float を使ってはいけません — 丸め誤差が蓄積します。レガシーなのが、serial で、GENERATED AS IDENTITY を推奨しましょう。ロケールを意識し不柔軟なのが、money 型です — 代わりに numeric(10,2) を。隙間のある自動増分では、ロールバック/クラッシュでシーケンスが数をスキップし得ます(パフォーマンスのための設計です)。
# integer types (use the smallest that fits)
smallint -- 2 bytes, -32768 to 32767
integer -- 4 bytes, -2B to 2B
bigint -- 8 bytes, very large
# auto-incrementing (use identity, not serial)
id integer GENERATED ALWAYS AS IDENTITY
# decimal/numeric (exact)
numeric(precision, scale) -- e.g. numeric(10,2) for currency
decimal -- alias for numeric
# floating point (inexact, faster)
real -- 4 bytes, 6 decimal digits
double precision -- 8 bytes, 15 decimal digits
# special
serial / bigserial -- legacy auto-increment (prefer identity)
money -- currency (prefer numeric)
# examples
CREATE TABLE products (
price numeric(10,2), -- $99999999.99
weight real, -- 1.5 (kg)
quantity integer DEFAULT 0
);テキストと文字型
PostgreSQL では、varchar、char、text が同一の性能を持ちます。無制限には text を、長さの制約が必要なときには varchar(n) を推奨しましょう。スペースで埋められ、まれにしか有用でないのが、char(n) です。メタデータのみの(速い)操作であるのが、varchar の長さ変更です。% と _ のワイルドカードを使うのが LIKE で、大文字小文字を区別しないのが、ILIKE、POSIX 正規表現が ~ です。全文検索には、tsvector/tsquery を(後にカバーします)。テキストは常に text/varchar として保存し、bytea としてではありません。
# three text types (all variable-length)
varchar(n) -- varchar with max length
char(n) -- fixed-length, padded with spaces
text -- unlimited length (preferred)
# in practice, all three perform the same
# varchar(n) adds a length check; char(n) pads (rarely useful)
CREATE TABLE articles (
title varchar(200) NOT NULL,
slug varchar(100) UNIQUE,
body text,
summary text DEFAULT ''
);
# string functions
SELECT length(body), char_length(title), octet_length(title)
FROM articles;
# change length limit (fast, no rewrite)
ALTER TABLE articles ALTER COLUMN title TYPE varchar(500);
# pattern matching
SELECT * FROM articles WHERE title LIKE '%postgres%';
SELECT * FROM articles WHERE title ILIKE '%postgres%'; -- case-insensitive
SELECT * FROM articles WHERE slug ~ '^[a-z0-9-]+$'; -- regex日付と時刻型
タイムスタンプには必ず timestamptz(timestamp ではなく)を使いましょう — UTC で保存し、表示時にセッションのタイムゾーンへ変換します。マルチタイムゾーンのアプリでバグを引き起こすのが、(tz なしの)naive な timestamp です。算術に強力なのが、interval です(「1 day」、「2 hours」、「3 months」)。単位へ切り下げるのが、date_trunc です。柔軟なのが、to_char/to_timestamp の形式文字列です。セッションかロールごとにタイムゾーンを設定しましょう — データはかかわらず UTC で保存されます。
# date/time types
date -- date only (4 bytes)
time -- time only (8 bytes)
timetz -- time with time zone (12 bytes)
timestamp -- timestamp without tz (8 bytes)
timestamptz -- timestamp WITH time zone (8 bytes, preferred)
interval -- time span (16 bytes)
# always use timestamptz for timestamps
CREATE TABLE events (
id serial PRIMARY KEY,
created_at timestamptz DEFAULT now(),
scheduled timestamptz,
duration interval
);
# common operations
SELECT now(); -- current timestamp
SELECT current_date; -- today
SELECT created_at + interval '1 hour'; -- add time
SELECT age('2025-01-01', '2024-01-01'); -- interval between
SELECT date_trunc('month', created_at); -- truncate to month
SELECT to_char(created_at, 'YYYY-MM-DD HH:MI:SS');
SELECT to_timestamp('2025/01/15', 'YYYY/MM/DD');
SELECT EXTRACT(YEAR FROM created_at);
# timezone handling
SET timezone = 'UTC';
SET timezone = 'Asia/Shanghai';JSON と JSONB
バイナリの JSON であるのが、jsonb です。クエリとインデックスが速く、挿入がわずかに遅く、キーの順序を保ちません。すべての新しい作業には jsonb を使いましょう。jsonb を返すのが、-> で、テキストを返すのが、->> です。最も強力な JSON 演算子であり、GIN インデックスと良く機能するのが、@>(包含)です。パスを修正するのが、jsonb_set、オブジェクトをマージするのが ||、キーを取り除くのが、- です。JSON フィールドの頻繁なクエリのためには、抽出された値の上に、GIN インデックスか式インデックスを加えましょう。リレーショナルデータベース内の柔軟なスキーマに理想的なのが、JSONB です。
# json stores text; jsonb stores binary (faster, preferred)
CREATE TABLE products (
id serial PRIMARY KEY,
data jsonb
);
# insert JSON
INSERT INTO products (data) VALUES
('{"name": "Widget", "price": 19.99, "tags": ["sale", "new"]}');
# query JSON fields
SELECT data->'name' FROM products; -- returns jsonb
SELECT data->>'name' FROM products; -- returns text
SELECT data->'tags'->0 FROM products; -- first tag (jsonb)
# filter by JSON
SELECT * FROM products WHERE data->>'name' = 'Widget';
SELECT * FROM products WHERE data @> '{"tags": ["sale"]}'; -- containment
# modify JSON
UPDATE products SET data = jsonb_set(data, '{price}', '29.99');
UPDATE products SET data = data || '{"sale": true}'; -- merge
UPDATE products SET data = data - 'sale'; -- remove key
# index JSON (GIN index)
CREATE INDEX idx_products_data ON products USING GIN (data);
CREATE INDEX idx_products_name ON products ((data->>'name'));配列とカスタム型
(ほとんどの言語と違い)1 起点であるのが、PostgreSQL の配列です。主要なクエリ演算子が、ANY と @> で、配列を行へ展開する(結合に最適)のが、unnest() です。強力ですが、賢明に配列を使いましょう — 配列要素のクエリ/フィルタリングを頻繁に行うなら、正規化されたテーブルの方が良いかもしれません。挿入時に順序付けされ検証されるのが、Enum です。構造化された列を可能にするのが、composite タイプです。複雑なネストデータには、composite の配列の代わりに JSONB を検討しましょう。
# array columns
CREATE TABLE teams (
id serial PRIMARY KEY,
name text,
members text[] -- array of text
);
INSERT INTO teams (name, members) VALUES
('Engineering', ARRAY['Alice', 'Bob', 'Carol']),
('Sales', '{"Dave", "Eve"}');
# query arrays
SELECT * FROM teams WHERE 'Alice' = ANY(members);
SELECT * FROM teams WHERE members @> ARRAY['Alice']; -- contains
SELECT members[1] FROM teams; -- 1-indexed!
SELECT array_length(members, 1) FROM teams;
SELECT unnest(members) FROM teams WHERE id = 1; -- expand to rows
# custom enum type
CREATE TYPE mood AS ENUM ('happy', 'sad', 'neutral');
CREATE TABLE persons (id serial PRIMARY KEY, current_mood mood);
INSERT INTO persons (current_mood) VALUES ('happy');
# composite type
CREATE TYPE address AS (street text, city text, zip text);
CREATE TABLE contacts (id serial, addr address);CRUD 操作
INSERT
挿入/更新/削除された行を返し、挿入後の別の SELECT を不要にするのが、PostgreSQL の機能である RETURNING です。強力なのが、ON CONFLICT(UPSERT)です:提案された行を参照するのが、DO UPDATE の EXCLUDED で、静かにスキップするのが、DO NOTHING です。個々の挿入よりはるかに速いのが、マルチ行の挿入です。テーブル間でデータをコピーするのが、INSERT ... SELECT です。一括ロードには COPY を(さらに速い)— SQL のパースのオーバーヘッドをバイパスするからです。
# basic insert
INSERT INTO users (username, email, age)
VALUES ('alice', '[email protected]', 30);
# multi-row insert
INSERT INTO users (username, email) VALUES
('bob', '[email protected]'),
('carol', '[email protected]'),
('dave', '[email protected]');
# insert with RETURNING (get auto-generated values)
INSERT INTO users (username, email)
VALUES ('eve', '[email protected]')
RETURNING id, username, created_at;
# insert from a query (INSERT ... SELECT)
INSERT INTO archive_users (username, email)
SELECT username, email FROM users WHERE active = false;
# upsert (insert or update on conflict)
INSERT INTO users (username, email) VALUES ('alice', '[email protected]')
ON CONFLICT (username)
DO UPDATE SET email = EXCLUDED.email
RETURNING id;
# do nothing on conflict
INSERT INTO users (username, email) VALUES ('alice', '[email protected]')
ON CONFLICT (username) DO NOTHING;UPDATE
すべての行を更新する意図でない限り、WHERE を必ず含めましょう。更新での結合を可能にするのが(非標準だが強力)、UPDATE ... FROM です。何が変わったか示すのが、RETURNING です — 監査には不可欠です。単一のステートメントでの条件付き更新を可能にするのが、CASE 式です。値を変えない更新が no-op になります(WAL なし、ステートメントレベルのトリガー発火なし)。テーブルを肥大させ得るのが、大きな更新であるため — バッチ化し、その後に VACUUM しましょう。
# basic update
UPDATE users SET email = '[email protected]' WHERE id = 1;
# update multiple columns
UPDATE users
SET email = '[email protected]', age = 31, updated_at = now()
WHERE id = 1;
# update with RETURNING
UPDATE users SET role = 'admin' WHERE id = 1
RETURNING id, username, role;
# update from another table
UPDATE posts p
SET title = p.title || ' (archived)'
FROM users u
WHERE p.user_id = u.id AND u.role = 'admin';
# conditional update with CASE
UPDATE products
SET price = CASE
WHEN price < 10 THEN price * 1.2
WHEN price < 50 THEN price * 1.1
ELSE price
END;
# update all rows (be careful!)
UPDATE users SET status = 'active';DELETE と TRUNCATE
行を 1 行ずつ取り除くのが、DELETE です(トリガーを発火し、トランザクションで見えます)。すべての行を即座に取り除くのが、TRUNCATE です(DDL、同じやり方ではトランザクション的でなく、RESTART IDENTITY でシーケンスをリセットします)。テーブル全体のクリアには TRUNCATE を使いましょう — 桁違いに速いです。このテーブルを参照する外部キー付きのテーブルを truncate するのが、CASCADE です。監査ログに有用なのが、RETURNING 付きの DELETE です。DELETE には必ず WHERE を使いましょう — なければ、(トランザクションの中なら安全に、コミットすれば破滅的に)すべてを削除します。
# basic delete
DELETE FROM users WHERE id = 1;
# delete with RETURNING
DELETE FROM users WHERE active = false
RETURNING id, username;
# delete based on a join
DELETE FROM posts
WHERE user_id IN (
SELECT id FROM users WHERE role = 'deleted'
);
# delete using USING (join syntax)
DELETE FROM posts p
USING users u
WHERE p.user_id = u.id AND u.status = 'deleted';
# truncate (fast, resets sequences, DDL-like)
TRUNCATE TABLE posts;
TRUNCATE TABLE posts, comments; -- multiple tables
TRUNCATE TABLE posts RESTART IDENTITY; -- reset sequences
TRUNCATE TABLE posts CASCADE; -- also truncates FK refs
# delete all rows (slower than TRUNCATE, but transactional)
DELETE FROM posts;SELECT の基本
グループごとの最初の行を返す PostgreSQL の拡張が、DISTINCT ON です。「カテゴリごとの最新」のクエリに強力です(DISTINCT ON の列を最初に ORDER BY しなければなりません)。単純なページネーションだが、大きなオフセットでは遅いのが、LIMIT/OFFSET です。より良いパフォーマンスのためには、キーセットのページネーション(WHERE id > last_id)を使いましょう。スキーマ変更時の驚きを避けるため、本番では(SELECT * ではなく)列を必ず明示的に指定しましょう。SQL 標準の構文が、FETCH FIRST n ROWS ONLY です。
# basic select
SELECT id, username, email FROM users;
# filtering
SELECT * FROM users WHERE age >= 18 AND role = 'user';
SELECT * FROM users WHERE role IN ('admin', 'moderator');
SELECT * FROM users WHERE age BETWEEN 18 AND 65;
SELECT * FROM users WHERE email IS NOT NULL;
SELECT * FROM users WHERE username LIKE 'al%';
# sorting and limiting
SELECT * FROM users ORDER BY created_at DESC LIMIT 10;
SELECT * FROM users ORDER BY age DESC, username ASC
OFFSET 20 LIMIT 10; -- pagination (or use FETCH)
# distinct
SELECT DISTINCT role FROM users;
SELECT DISTINCT ON (user_id) * FROM posts
ORDER BY user_id, created_at DESC; -- latest post per user
# column aliases and expressions
SELECT
username AS name,
EXTRACT(YEAR FROM created_at) AS join_year,
age * 365 AS age_in_days
FROM users;MERGE(PG15+)
(SQL 標準、PG15+ の)MERGE が、ON CONFLICT より強力です:一致条件に基づき、1 つのステートメントで挿入、更新、「かつ」削除ができます。複雑な同期操作(ETL、データウェアハウスのマージ)に使いましょう。単純な upsert には、より簡潔で広くサポートされているのが、ON CONFLICT です。ソース(テーブルかクエリ)と一致条件を必要とするのが、MERGE です。アクションを定義するのが、WHEN MATCHED/NOT MATCHED 句です。きめ細かい制御のための省略可能な AND 条件を、各分岐が持てます。
# MERGE: insert, update, or delete in one statement (PG15+)
MERGE INTO products p
USING new_prices n
ON p.sku = n.sku
WHEN MATCHED AND p.price != n.price THEN
UPDATE SET price = n.price, updated_at = now()
WHEN MATCHED AND n.discontinued = true THEN
DELETE
WHEN NOT MATCHED THEN
INSERT (sku, name, price) VALUES (n.sku, n.name, n.price);
# simpler upsert (still works, often preferred)
INSERT INTO products (sku, name, price) VALUES ('A1', 'Widget', 9.99)
ON CONFLICT (sku)
DO UPDATE SET price = EXCLUDED.price;
# MERGE with a source query
MERGE INTO inventory i
USING (SELECT product_id, sum(qty) AS total FROM orders GROUP BY product_id) o
ON i.product_id = o.product_id
WHEN MATCHED THEN UPDATE SET quantity = i.quantity - o.total;クエリとフィルタリング
WHERE と条件
大文字小文字を区別しない LIKE(PostgreSQL の拡張)が、ILIKE です。POSIX 正規表現が ~(大文字小文字を区別)、大文字小文字を区別しないのが ~*、否定が !~ です。NULL を比較可能な値として扱うのが、IS DISTINCT FROM です(NULL = NULL は真でなく NULL です)。配列とサブクエリで機能するのが、ANY/ALL です。複雑なテキストのマッチングには、パフォーマンスのために全文検索(tsvector)か trigram のインデックス(pg_trgm)を検討しましょう。大きなテーブルには、WHERE 句で使われる列を必ずインデックスしましょう。
# comparison operators
SELECT * FROM products WHERE price > 100;
SELECT * FROM products WHERE price <> 0; -- not equal
SELECT * FROM products WHERE name IS DISTINCT FROM 'Test';
# logical operators
SELECT * FROM products
WHERE (price > 50 AND category = 'electronics')
OR (price < 10 AND category = 'books');
# IN, BETWEEN, LIKE/ILIKE
SELECT * FROM products WHERE category IN ('books', 'toys');
SELECT * FROM products WHERE price BETWEEN 10 AND 50;
SELECT * FROM products WHERE name LIKE '_o%'; -- second char 'o'
SELECT * FROM products WHERE name ILIKE '%widget%';
# IS NULL / IS NOT NULL
SELECT * FROM products WHERE description IS NULL;
SELECT * FROM products WHERE description IS NOT DISTINCT FROM NULL;
# ANY/ALL with arrays
SELECT * FROM products WHERE id = ANY(ARRAY[1,2,3]);
SELECT * FROM products WHERE price > ALL(ARRAY[10, 20, 30]);
# regex matching
SELECT * FROM products WHERE name ~ '^[A-C]'; -- starts with A-C
SELECT * FROM products WHERE name ~* 'widget'; -- case-insensitive
SELECT * FROM products WHERE name !~ 'test'; -- does not matchORDER BY とページネーション
大きな dataset では OFFSET より劇的に速いのが、(WHERE id > last_id の)キーセットのページネーションです — OFFSET が行をスキャンし破棄します。大きなテーブルで遅いのが、ORDER BY random() で、統計的なサンプリングには TABLESAMPLE を使いましょう。NULL がどこでソートされるかを制御するのが、NULLS FIRST/LAST です(ASC/DESC によりデフォルトが異なります)。ページネーション時には必ず ORDER BY を含めましょう — なければ、行の順序が未定義です。安定したページネーションのためには、一意の列で順序付けましょう。
# basic sorting
SELECT * FROM users ORDER BY created_at DESC;
SELECT * FROM users ORDER BY last_name ASC, first_name ASC;
# sort by expression
SELECT * FROM products ORDER BY (price * 1.1) DESC;
SELECT * FROM events ORDER BY created_at::date;
# NULLS handling
SELECT * FROM users ORDER BY last_login DESC NULLS LAST;
SELECT * FROM users ORDER BY last_login DESC NULLS FIRST;
# LIMIT/OFFSET pagination (simple but slow for large offsets)
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 0; -- page 1
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 10; -- page 2
# keyset pagination (faster, use for large datasets)
SELECT * FROM users WHERE id > 100 ORDER BY id LIMIT 10;
# FETCH (SQL standard)
SELECT * FROM users ORDER BY id FETCH FIRST 10 ROWS ONLY;
# random rows
SELECT * FROM products ORDER BY random() LIMIT 5; -- slow on large tables
SELECT * FROM products TABLESAMPLE BERNOULLI(1); -- 1% sampleGROUP BY と集約
グループ化列により行を折りたたむのが、GROUP BY で、グループごとに計算されるのが、集約(count、sum、avg、min、max)です。グループをフィルタするのが、HAVING です(グループ化の前に行をフィルタするのが、WHERE です)。各レベルで小計を加えるのが、ROLLUP、すべての組み合わせを加えるのが、CUBE、欲しいグループ化を正確に指定できるのが、GROUPING SETS です — レポーティングにすべて強力です。行を数えるのが count(*) で、非 null の値を数えるのが、count(column) です。一意のカウントには、count(DISTINCT col) を。
# basic grouping
SELECT category, count(*) FROM products GROUP BY category;
SELECT user_id, sum(amount) FROM orders GROUP BY user_id;
# multiple aggregates
SELECT
category,
count(*) AS total,
avg(price) AS avg_price,
min(price) AS min_price,
max(price) AS max_price,
sum(price) AS total_value
FROM products
GROUP BY category;
# HAVING (filter on aggregates, like WHERE for groups)
SELECT user_id, count(*) AS order_count
FROM orders
GROUP BY user_id
HAVING count(*) > 5;
# GROUP BY with multiple columns
SELECT category, status, count(*)
FROM products
GROUP BY category, status
ORDER BY category, status;
# GROUP BY ROLLUP/CUBE (subtotals)
SELECT category, status, count(*)
FROM products
GROUP BY ROLLUP (category, status); -- adds subtotals and grand total
# GROUP BY GROUPING SETS
SELECT category, status, count(*)
FROM products
GROUP BY GROUPING SETS ((category, status), (category), ());DISTINCT と集合の操作
結果を組み合わせ重複を取り除くのが、UNION です(遅い)。重複を保つのが、UNION ALL です(速く、重複がないと分かっているか気にしないなら推奨)。共通の行を返すのが、INTERSECT で、差を返すのが、EXCEPT です。互換的な列のタイプと数を要求するのが、集合の操作です。PostgreSQL の宝石が、DISTINCT ON です:ORDER BY に基づきグループごとの最初の行を返します — 「カテゴリごとの最新/最も高価」のクエリに完璧です。連鎖できるのが、すべての集合の操作ですが、優先順位を制御するには括弧を使いましょう。
# distinct rows
SELECT DISTINCT category FROM products;
SELECT DISTINCT category, status FROM products;
# DISTINCT ON (first row per group)
SELECT DISTINCT ON (category) *
FROM products
ORDER BY category, price DESC; -- most expensive per category
# UNION (combine, remove duplicates)
SELECT 'user' AS type, username FROM users
UNION
SELECT 'admin' AS type, username FROM admins;
# UNION ALL (faster, keeps duplicates)
SELECT username FROM active_users
UNION ALL
SELECT username FROM inactive_users;
# INTERSECT (common rows)
SELECT product_id FROM orders_2024
INTERSECT
SELECT product_id FROM orders_2025;
# EXCEPT (rows in first but not second)
SELECT product_id FROM all_products
EXCEPT
SELECT product_id FROM discontinued_products;
# set operations with ORDER BY (applies to the whole result)
SELECT username FROM users
UNION
SELECT username FROM admins
ORDER BY username;CASE と条件ロジック
SQL へ if/then/else のロジックを加えるのが、CASE 式です。値に一致するのが、simple CASE で、条件を評価するのが、searched CASE です。条件付きのカウントのため、集約内の CASE よりきれいなのが、(PostgreSQL の)FILTER 句です。SELECT、WHERE、ORDER BY、そして GROUP BY さえ、CASE が現れ得ます。データベースに属すべき、ピボット、条件付きの整形、ビジネスロジックに使いましょう。複雑なマルチ分岐のロジックには、関数かビューを検討しましょう。
# simple CASE
SELECT
name,
CASE category
WHEN 'book' THEN 'Media'
WHEN 'laptop' THEN 'Electronics'
ELSE 'Other'
END AS category_group
FROM products;
# searched CASE (more flexible)
SELECT
name,
CASE
WHEN price < 10 THEN 'Cheap'
WHEN price < 50 THEN 'Moderate'
WHEN price < 100 THEN 'Expensive'
ELSE 'Luxury'
END AS price_tier
FROM products;
# CASE in aggregate (pivot-like)
SELECT
count(*) AS total,
count(*) FILTER (WHERE status = 'active') AS active_count,
count(*) FILTER (WHERE status = 'inactive') AS inactive_count
FROM users;
# CASE for conditional sorting
SELECT * FROM products
ORDER BY
CASE WHEN featured THEN 0 ELSE 1 END,
name;結合
INNER と OUTER の結合
一致する行だけを返すのが、INNER JOIN です。すべての左の行を保ち(一致がなければ右の列が NULL)、これが「存在すれば関連データを含める」の最もよくある結合です。めったに使われないのが(LEFT として書き直す)、RIGHT JOIN です。すべてを保つのが、FULL OUTER JOIN です。一致のない行を見つけるアンチ結合のパターンが、LEFT JOIN の後の WHERE p.id IS NULL です。曖昧さを避けるため、列名は必ずテーブルの別名(u.id、p.user_id)で修飾しましょう。
# INNER JOIN (only matching rows)
SELECT u.username, p.title
FROM users u
INNER JOIN posts p ON u.id = p.user_id;
# LEFT JOIN (all left rows, matching right or NULL)
SELECT u.username, p.title
FROM users u
LEFT JOIN posts p ON u.id = p.user_id;
# RIGHT JOIN (all right rows, matching left or NULL)
SELECT u.username, p.title
FROM users u
RIGHT JOIN posts p ON u.id = p.user_id;
# FULL OUTER JOIN (all rows from both, NULLs where no match)
SELECT u.username, p.title
FROM users u
FULL OUTER JOIN posts p ON u.id = p.user_id;
# find rows with no match (anti-join pattern)
SELECT u.username
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
WHERE p.id IS NULL; -- users with no postsマルチテーブルの結合
多くのテーブルを PostgreSQL は結合できますが、各結合がコストを加えるため — 結合がインデックスを使うことを確保しましょう。階層データに有用なのが、自己結合です(深い階層には再帰 CTE を検討しましょう)。集約での結合では、(count(*) でなく)count(p.id) 付きの LEFT JOIN が、投稿のないユーザーに 0 を与えます。オプティマイザーが結合の順序を選びますが、明快なクエリを書くことが助けになります。複雑なマルチテーブルのクエリには、ビューの作成を検討しましょう。
# join three tables
SELECT u.username, p.title, c.text AS comment
FROM users u
JOIN posts p ON u.id = p.user_id
JOIN comments c ON p.id = c.post_id;
# join with different conditions
SELECT
u.username,
p.title,
l.name AS liked_by
FROM posts p
JOIN users u ON p.user_id = u.id
LEFT JOIN likes l ON p.id = l.post_id AND l.user_id != u.id;
# self-join (e.g., employees and their managers)
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;
# join with aggregation
SELECT u.username, count(p.id) AS post_count
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id, u.username
ORDER BY post_count DESC;CROSS JOIN と LATERAL
デカルト積を作るのが(A のすべての行と B のすべての行)、CROSS JOIN です。めったに欲しいものではありませんが、データ生成のための generate_series と共に有用です。強力なのが、LATERAL です:サブクエリが外側のクエリの列を参照でき、行ごとの関数のように。グループごとのトップ N のクエリに理想的です(単純なケースには、窓関数よりはるかにきれいです)。一致のない行を含めるのが、LEFT JOIN LATERAL ... ON true です。
# CROSS JOIN (Cartesian product — every combination)
SELECT u.username, c.name
FROM users u
CROSS JOIN categories c;
-- same as: SELECT ... FROM users, categories;
# useful for generating combinations
SELECT generate_series(1, 5) AS n, c.name
FROM categories c
CROSS JOIN generate_series(1, 5);
# LATERAL (subquery can reference outer query)
SELECT u.username, recent.title
FROM users u
CROSS JOIN LATERAL (
SELECT title FROM posts
WHERE user_id = u.id
ORDER BY created_at DESC
LIMIT 3
) recent;
# LATERAL with LEFT JOIN (include users with no posts)
SELECT u.username, recent.title
FROM users u
LEFT JOIN LATERAL (
SELECT title FROM posts WHERE user_id = u.id
ORDER BY created_at DESC LIMIT 3
) recent ON true;結合の戦略(EXPLAIN)
テーブルのサイズ、インデックス、統計に基づき、PostgreSQL が結合の戦略を選びます。O(N*M) であるのが、Nested Loop です — 小さい、またはインデックス付きのルックアップには問題ありません。より小さい入力上にハッシュテーブルを構築しプローブするのが、Hash Join で — 大きいソートされていない結合に優れています。両方の入力がソートされていることを要求するのが、Merge Join です — インデックスが順序を提供するとき素晴らしいです。本番で戦略を強制せず、オプティマイザーに決めさせましょう。本物のタイミングを見るには、EXPLAIN (ANALYZE) を使い、必要ならインデックス/統計を調整しましょう。
# see how PostgreSQL joins tables
EXPLAIN SELECT * FROM users u JOIN posts p ON u.id = p.user_id;
# join strategies you'll see:
# Nested Loop: for each left row, scan right (good for small/indexed)
# Hash Join: hash one table, probe with other (good for large unsorted)
# Merge Join: both sorted, merge (good for sorted/indexed large tables)
# force a join strategy (for testing, not production)
SET enable_nestloop = off;
SET enable_hashjoin = off;
SET enable_mergejoin = off;
# check actual execution stats
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM users u JOIN posts p ON u.id = p.user_id;
# join removal: PostgreSQL can remove unnecessary joins
# (e.g., LEFT JOIN to a table only used for FK validation)
EXPLAIN SELECT u.* FROM users u LEFT JOIN posts p ON u.id = p.user_id;結合のパフォーマンスのヒント
最も大きな影響のある結合の最適化が、外部キー列のインデックスです。PostgreSQL は FK のためにインデックスを自動作成し「ません」。ANALYZE で統計を新鮮に保ちましょう(auto-vacuum が行いますが、一括ロード後の手動 ANALYZE が助けになります)。結合列を関数で包むのを避けましょう(インデックスの使用を防ぎます)。必要な列だけを選びましょう。オプティマイザーがパーティションを剪定できるよう、巨大なテーブルにはパーティショニングを検討しましょう。pg_stat_statements で遅い結合を監視しましょう。
# always index foreign key columns
CREATE INDEX idx_posts_user_id ON posts(user_id);
# index join columns on both sides
CREATE INDEX idx_orders_product_id ON orders(product_id);
CREATE INDEX idx_products_id ON products(id); -- PK already indexed
# composite index for multi-column joins
CREATE INDEX idx_posts_user_created ON posts(user_id, created_at);
# analyze tables for accurate statistics
ANALYZE users;
ANALYZE posts;
# avoid joining on expressions (prevents index use)
-- BAD: JOIN ON lower(a.email) = lower(b.email)
-- GOOD: JOIN ON a.email = b.email (normalize case on insert)
# limit columns selected (reduces I/O)
SELECT u.username, p.title -- not SELECT *
FROM users u JOIN posts p ON u.id = p.user_id;
# partition large tables for join pruning
SELECT * FROM orders o
JOIN products p ON o.product_id = p.id
WHERE o.order_date >= '2025-01-01'; -- prunes partitions集約と窓関数
集約関数
count/sum/avg を超える豊かな集約を PostgreSQL が持ちます。値を文字列/配列へ連結するのが、string_agg と array_agg です — N+1 クエリの回避に最適です。真偽値を集約するのが、bool_or/bool_and です。正確なパーセンタイルを計算するのが、percentile_cont です(中央値 = 0.5)— 分析に非常に有用です。最も頻繁な値を返すのが、mode() です。条件付き集約には、(CASE よりきれいな)FILTER 句(count(*) FILTER (WHERE...))です。出力の順序を制御するのが、集約内の ORDER BY です。
# standard aggregates
SELECT
count(*) AS total_rows,
count(distinct category) AS unique_categories,
sum(price) AS total_value,
avg(price) AS avg_price,
min(price) AS min_price,
max(price) AS max_price,
stddev(price) AS std_dev,
variance(price) AS variance
FROM products;
# string aggregation
SELECT
user_id,
string_agg(tag, ', ' ORDER BY tag) AS tags
FROM post_tags
GROUP BY user_id;
# array aggregation
SELECT
user_id,
array_agg(title ORDER BY created_at DESC) AS recent_titles
FROM posts
GROUP BY user_id;
# boolean aggregation
SELECT
bool_or(published) AS any_published,
bool_and(published) AS all_published
FROM posts;
# statistical aggregates
SELECT
percentile_cont(0.5) WITHIN GROUP (ORDER BY price) AS median,
percentile_cont(0.95) WITHIN GROUP (ORDER BY price) AS p95,
mode() WITHIN GROUP (ORDER BY price) AS most_common
FROM products;窓関数
現在の行に関連する行の集合をまたいで計算するのが、窓関数です — GROUP BY のように行を折りたたみません。窓のグループを定義するのが、PARTITION BY で、内部の順序を定義するのが、ORDER BY です。常に一意なのが、ROW_NUMBER で、同点の後で数をスキップするのが、RANK、スキップしないのが、DENSE_RANK です。他の行へのアクセスが、LAG/LEAD です — 時系列の分析に完璧です。窓に含まれる行を制御するのが、フレーム句(ROWS BETWEEN ...)です。WHERE/GROUP BY/HAVING の後に評価されるのが、窓関数です。
# ROW_NUMBER: unique sequential number
SELECT
title,
price,
ROW_NUMBER() OVER (ORDER BY price DESC) AS rank
FROM products;
# RANK and DENSE_RANK (handle ties)
SELECT
title,
category,
price,
RANK() OVER (PARTITION BY category ORDER BY price DESC) AS cat_rank,
DENSE_RANK() OVER (PARTITION BY category ORDER BY price DESC) AS dense_rank
FROM products;
# LAG and LEAD (compare to previous/next row)
SELECT
date,
revenue,
LAG(revenue, 1) OVER (ORDER BY date) AS prev_day,
revenue - LAG(revenue, 1) OVER (ORDER BY date) AS daily_change,
LEAD(revenue, 1) OVER (ORDER BY date) AS next_day
FROM daily_sales;
# running total
SELECT
date,
revenue,
SUM(revenue) OVER (ORDER BY date) AS running_total,
AVG(revenue) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_7d
FROM daily_sales;窓のフレームと NTILE
窓関数がどの行を見るかを制御するのが、窓のフレームです。集約のためのデフォルトのフレームが、(累計の)RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW です。LAST_VALUE では、フレームを UNBOUNDED FOLLOWING へ広げなければならず、さもなければ(デフォルトのフレームが現在で終わるため)現在の行を返します。行を N バケットへ分割するのが、NTILE です — 四分位数/十分位数に有用です。フレームの境界の行からの値を返すのが、FIRST_VALUE/LAST_VALUE です。窓関数をマスターする鍵が、フレームの理解です。
# frame types
# ROWS: exact row count
# RANGE: logical range (e.g., same value)
# GROUPS: groups of peer rows
SELECT
date,
revenue,
# running sum from start to current
SUM(revenue) OVER (ORDER BY date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative,
# 7-day moving average
AVG(revenue) OVER (ORDER BY date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_7d,
# sum of all rows (no frame = entire partition)
SUM(revenue) OVER () AS grand_total
FROM daily_sales;
# NTILE: divide rows into N equal buckets
SELECT
name,
price,
NTILE(4) OVER (ORDER BY price DESC) AS price_quartile
FROM products;
# FIRST_VALUE / LAST_VALUE
SELECT
name,
category,
price,
FIRST_VALUE(name) OVER (PARTITION BY category ORDER BY price DESC) AS most_expensive,
LAST_VALUE(name) OVER (
PARTITION BY category ORDER BY price DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS least_expensive
FROM products;FILTER 句
集約内の CASE よりきれいで、しばしば速いのが、PostgreSQL の拡張である FILTER 句です。複数の条件付き集約が必要な、ピボット風のレポートに特に有用です。クエリがテーブルを一度スキャンし、すべての集約を 1 パスで計算します — 複数のサブクエリよりはるかに効率的です。(多くの PG の拡張と違い)SQL 標準の一部が、FILTER であり、他のデータベースもサポートします。
# FILTER (PostgreSQL extension, cleaner than CASE)
SELECT
count(*) AS total,
count(*) FILTER (WHERE status = 'active') AS active,
count(*) FILTER (WHERE status = 'inactive') AS inactive,
count(*) FILTER (WHERE status = 'banned') AS banned,
sum(amount) FILTER (WHERE status = 'active') AS active_revenue
FROM orders
GROUP BY user_id;
# equivalent with CASE (more verbose)
SELECT
count(*) AS total,
count(CASE WHEN status = 'active' THEN 1 END) AS active,
sum(CASE WHEN status = 'active' THEN amount ELSE 0 END) AS active_revenue
FROM orders
GROUP BY user_id;
# FILTER with multiple aggregates in one pass (efficient)
SELECT
category,
count(*) FILTER (WHERE price > 100) AS expensive_count,
avg(price) FILTER (WHERE price > 100) AS expensive_avg,
count(*) FILTER (WHERE price <= 100) AS cheap_count,
avg(price) FILTER (WHERE price <= 100) AS cheap_avg
FROM products
GROUP BY category;共通テーブル式(CTE)
クエリの読みやすさを改善し、サブクエリの再利用を可能にするのが、CTE(WITH 句)です。デフォルトで非再帰 CTE をインライン化するのが、PostgreSQL 12+ です(そのため、性能がサブクエリと一致します)。12 より前では、最適化のフェンスが、CTE でした。階層/ツリーデータに強力なのが、RECURSIVE の CTE です — アンカーのクエリが種を蒔き、再帰の部分が成長します。1 つのステートメントでの複雑なデータ移動(あるテーブルからの削除、別のテーブルへのアーカイブ)を可能にするのが、RETURNING 付きの CTE です。複雑なクエリを読みやすいステップに分解するのに、CTE を。
# basic CTE (WITH clause)
WITH active_users AS (
SELECT id, username FROM users WHERE active = true
)
SELECT au.username, count(p.id) AS post_count
FROM active_users au
LEFT JOIN posts p ON au.id = p.user_id
GROUP BY au.username;
# multiple CTEs (can reference each other)
WITH user_stats AS (
SELECT user_id, count(*) AS post_count FROM posts GROUP BY user_id
),
top_users AS (
SELECT user_id FROM user_stats WHERE post_count > 10
)
SELECT u.username, us.post_count
FROM users u
JOIN top_users tu ON u.id = tu.user_id
JOIN user_stats us ON u.id = us.user_id;
# recursive CTE (tree traversal)
WITH RECURSIVE org_tree AS (
SELECT id, name, manager_id, 0 AS depth
FROM employees WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, e.manager_id, ot.depth + 1
FROM employees e
JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT id, name, depth FROM org_tree ORDER BY depth;
# CTE for DML (data modification)
WITH deleted AS (
DELETE FROM users WHERE active = false RETURNING id
)
INSERT INTO archived_users (user_id)
SELECT id FROM deleted;インデックス
B-Tree インデックス
デフォルトで最もよくあるインデックスタイプが、B-tree です — 等価、範囲、ソートに対応します。左から右へ検索されるのが、複合インデックスであるため、等価列を先に置き、その後に範囲/ソート列です。スペースを節約し速いのが、(WHERE 句の)部分インデックスです — よくあるフィルタ済みのクエリ(WHERE active = true など)に使いましょう。関数の結果のインデックスを可能にするのが、式インデックスです。書き込みをブロックせず構築するのが(遅いが本番には安全)、CONCURRENTLY です。外部キーを必ずインデックスしましょう!
# create a B-tree index (default)
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_posts_created ON posts(created_at DESC);
# unique index (enforces uniqueness)
CREATE UNIQUE INDEX idx_users_username ON users(username);
# composite index (order matters!)
CREATE INDEX idx_posts_user_created
ON posts(user_id, created_at DESC);
# partial index (only rows matching the condition)
CREATE INDEX idx_active_users ON users(last_login)
WHERE active = true;
# expression index (index on a function)
CREATE INDEX idx_users_lower_email ON users(lower(email));
# query must use the same expression:
# SELECT * FROM users WHERE lower(email) = '[email protected]'
# check index usage
SELECT * FROM pg_stat_user_indexes WHERE relname = 'users';
# drop an index
DROP INDEX IF EXISTS idx_users_email;
DROP INDEX CONCURRENTLY idx_users_email; -- no lock, slowerGIN と GiST インデックス
各行が複数のインデックス付きの値(JSONB のキー、配列の要素、テキストのトークン)を持つとき、理想的なのが、GIN インデックスです。@>(包含)、?(キーの存在)、全文の @@ 演算子をサポートします。大きいが、これらのクエリを効率的に扱うのが、GIN インデックスです。幾何データ(点、多角形)とレンジタイプ向けなのが、GiST で — 重なり(&&)、包含(@>)、被包含(<@)をサポートします。構築/更新が B-tree より遅いのが、GIN と GiST の両方ですが、B-tree ができないクエリを可能にします。JSONB と全文には GIN を、空間とレンジのクエリには、GiST を。
# GIN (Generalized Inverted Index) — for multi-value columns
# great for JSONB, arrays, full-text search
CREATE INDEX idx_products_data ON products USING GIN (data); -- JSONB
CREATE INDEX idx_posts_tags ON posts USING GIN (tags); -- array
CREATE INDEX idx_articles_search ON articles USING GIN (search_vector); -- full-text
# query patterns that use GIN
SELECT * FROM products WHERE data @> '{"tags": ["sale"]}';
SELECT * FROM posts WHERE tags && ARRAY['python'];
SELECT * FROM articles WHERE search_vector @@ to_tsquery('postgres & index');
# GiST (Generalized Search Tree) — for geometric/range data
CREATE INDEX idx_locations ON places USING GIST (location); -- point/polygon
CREATE INDEX idx_events ON events USING GIST (during); -- tsrange
# geometric queries
SELECT * FROM places
WHERE location <@ box '(0,0),(10,10)';
# range queries
SELECT * FROM events
WHERE during && tsrange('2025-01-01', '2025-02-01');BRIN とその他のインデックスタイプ
極めてコンパクトな(GB でなく KB)のが、BRIN(Block Range INdex)です。ブロックの範囲ごとに min/max を保存します。物理的な行の順序がクエリの順序と一致するとき(新しいデータが追記される時系列のログなど)、効果的です。ほとんどのブロックを取り除く「十分に良い」フィルタリングを与えるのが、BRIN で、PostgreSQL が残りの行を確認します。巨大な追記のみのテーブルにとって、ゲームチェンジャーが BRIN です。等価にしか対応しない(レンジでない)が、ポイントルックアップに速いのが、ハッシュインデックスです。IP ルーティングテーブルのような、不均衡なデータに適したのが、SP-GiST です。
# BRIN (Block Range Index) — tiny, for naturally ordered data
# best for huge tables where data is physically ordered (e.g., time-series)
CREATE INDEX idx_logs_timestamp ON logs USING BRIN (timestamp);
# index is kilobytes, not gigabytes!
# BRIN works by storing min/max for each block range
# effective when physical order matches logical order (e.g., append-only logs)
# compare sizes:
# B-tree on 1B rows: ~20GB
# BRIN on 1B rows: ~2MB (10000x smaller)
# SP-GiST (space-partitioned GiST) — for non-balanced data
CREATE INDEX idx_prefix ON routes USING SPGiST (prefix); -- e.g., IP routing
# Hash index (equality only, crash-safe since PG10)
CREATE INDEX idx_users_session ON users USING HASH (session_token);
# only supports = operator, not ranges or sorting
# check what index types are available
SELECT amname FROM pg_am WHERE amtype = 'i';インデックスの保守と分析
pg_stat_user_indexes でインデックスの使用を監視しましょう — 未使用のインデックスがスペースを浪費し書き込みを遅くします。重複の除去(同じ列、同じテーブル)をしましょう。肥大化したインデックスを再構築するのが、REINDEX です(重い更新/削除で起きます)。ロックを避けるのが、(12+ の)CONCURRENTLY です。正常であるのが、インデックスの肥大です。autovacuum がインデックスを保守しますが、時々手動の REINDEX が要ります。非常に大きなインデックスには、REINDEX CONCURRENTLY か pg_repack(ロックなしで再構築するサードパーティツール)を検討しましょう。インデックスの変化は、ステージングで必ずテストしましょう。
# find unused indexes (candidates for removal)
SELECT
schemaname, relname, indexrelname,
idx_scan AS scans,
pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC;
# find duplicate indexes
SELECT pg_size_pretty(sum(pg_relation_size(idx))::bigint) AS size,
(array_agg(idx::text))[1] AS indexes
FROM (
SELECT indexrelid::regclass AS idx, indrelid::regclass AS rel
FROM pg_index
GROUP BY indrelid, indkey
HAVING count(*) > 1
) sub;
# REINDEX (rebuild a bloated index)
REINDEX INDEX idx_users_email;
REINDEX TABLE CONCURRENTLY users; -- PG12+, no lock
# check index bloat
SELECT
relname, pg_size_pretty(pg_relation_size(relid)) AS size,
pg_size_pretty(pg_indexes_size(relid)) AS index_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_relation_size(relid) DESC;インデックスの戦略
PK(自動インデックス)と FK(手動)から始めましょう。ホットなクエリの経路のためにインデックスを加えましょう。最も左の接頭辞のルールに従うのが、複合インデックスです — 選択性とクエリパターンにより列を順序付けましょう。行の取得なしで、(インデックスのみのスキャン)インデックスだけでクエリを満たさせるのが、(カバリングインデックスの)INCLUDE です — 読み取りが重いワークロードには巨大です。すべてをインデックスしてはいけません。各インデックスが書き込みのオーバーヘッドを加えます。pg_stat_user_indexes を使い、定期的に見直し刈り込みましょう。インデックスの使用を検証するには、必ず EXPLAIN を。
# 1. index primary keys (auto-created)
# 2. index foreign keys (NOT auto-created!)
CREATE INDEX idx_posts_user_id ON posts(user_id);
# 3. index columns in WHERE, JOIN, ORDER BY, GROUP BY
CREATE INDEX idx_users_status ON users(status);
CREATE INDEX idx_orders_created ON orders(created_at);
# 4. use composite indexes for multi-column queries
# leftmost prefix rule: (a, b, c) helps WHERE a=?, WHERE a=? AND b=?
# but NOT WHERE b=? alone
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);
# 5. partial indexes for common filters
CREATE INDEX idx_active_orders ON orders(created_at)
WHERE status = 'active';
# 6. covering indexes (INCLUDE for index-only scans)
CREATE INDEX idx_products_cat_price ON products(category, price)
INCLUDE (name); -- name available without heap fetch
# 7. don't over-index — every index slows writes
# drop indexes used < 10 times/month (check pg_stat_user_indexes)
# verify the index is used
EXPLAIN SELECT * FROM orders WHERE user_id = 1;トランザクションと同時実行
トランザクションの基本
ステートメントをアトミックな単位へグループ化するのが、トランザクションです — すべてが成功するか、すべてが失敗します。始めるのが BEGIN、保存するのが COMMIT、取り消すのが ROLLBACK です。トランザクション内の部分的なロールバックを可能にするのが、savepoint です — 複雑な操作でのエラー回復に有用です。デフォルトで、各ステートメントを PostgreSQL が自動コミットします。マルチステートメントの操作(送金、マルチ行の挿入)には、必ず明示的なトランザクションを使いましょう。ロックの競合とデッドロックを減らすため、トランザクションは短く保ちましょう。
# explicit transaction
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; -- or ROLLBACK to undo
# savepoints (partial rollback)
BEGIN;
INSERT INTO orders (user_id, amount) VALUES (1, 50);
SAVEPOINT order_inserted;
INSERT INTO order_items (order_id, product_id) VALUES (1, 100);
-- oops, wrong product
ROLLBACK TO order_inserted;
INSERT INTO order_items (order_id, product_id) VALUES (1, 200);
COMMIT;
# transaction control
BEGIN;
-- statements
SAVEPOINT my_savepoint;
-- more statements
ROLLBACK TO my_savepoint; -- undo back to savepoint
RELEASE my_savepoint; -- remove savepoint (commit its changes)
COMMIT;
-- or ROLLBACK to undo everything
# auto-commit (default — each statement is its own transaction)
SET autocommit = on; -- default分離レベル
ほとんどのアプリには良いのが、PostgreSQL のデフォルト(READ COMMITTED)です — ダーティリードはなく、同じトランザクション内の再読み込みが、新しいデータを見得ます。SQL 標準より強いのが、PostgreSQL の REPEATABLE READ です — (スナップショット分離を使い)ファントムも防ぎます。最も強く、競合するトランザクションを中止し得るのが、SERIALIZABLE です — アプリが、SQLSTATE 40001 で再試行しなければなりません。ほとんどの Web アプリには、READ COMMITTED で十分です。リトライロジック付きで、重要な正しさ(財務の台帳)のためには、SERIALIZABLE を。
# set isolation level for a transaction
BEGIN ISOLATION LEVEL READ COMMITTED; -- default
BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN ISOLATION LEVEL SERIALIZABLE;
# or set it for the session
SET default_transaction_isolation = 'serializable';
# READ COMMITTED (default):
# - each query sees committed data at query start
# - no dirty reads, but non-repeatable reads and phantoms possible
# REPEATABLE READ:
# - each query sees snapshot from transaction start
# - no non-repeatable reads, but serialization anomalies possible
# - PostgreSQL's RR is stronger than SQL standard (no phantoms!)
# SERIALIZABLE:
# - strongest isolation, behaves as if transactions ran one at a time
# - may abort with serialization failure (retry needed)
# - uses SSI (Serializable Snapshot Isolation)
# check current level
SHOW transaction_isolation;
# handle serialization failures (retry)
BEGIN ISOLATION LEVEL SERIALIZABLE;
-- work
COMMIT; -- may fail with SQLSTATE 40001 -> retry the transactionロックとデッドロック
更新のための行をロックするのが(同時修正を防ぐ)、SELECT FOR UPDATE です — 「読み、確認し、更新する」のパターンに有用です。整数キーを使うアプリケーションレベルのロックが、advisory ロックです — 分散プロセスの調整に最適です。2 つのトランザクションが互いを待つとき起きるのが、デッドロックです。PostgreSQL が検出し 1 つを中止します(捉えて再試行しましょう)。デッドロックを避けるには、トランザクションをまたいで一貫した順序でロックを取得しましょう。トランザクションは短く保ちましょう。塞がったセッションのためには、pg_locks を監視しましょう。
# row-level locks (acquired by UPDATE, DELETE, SELECT FOR UPDATE)
SELECT * FROM accounts WHERE id = 1 FOR UPDATE; -- lock row
SELECT * FROM accounts WHERE id = 1 FOR NO KEY UPDATE; -- weaker lock
SELECT * FROM accounts WHERE id = 1 FOR SHARE; -- allow reads, block writes
# advisory locks (application-level locks)
SELECT pg_advisory_lock(12345); -- session-level
SELECT pg_advisory_unlock(12345);
SELECT pg_try_advisory_lock(12345); -- non-blocking
SELECT pg_advisory_xact_lock(12345); -- transaction-level (auto-released)
# view active locks
SELECT pid, mode, granted, query
FROM pg_locks l JOIN pg_stat_activity a ON l.pid = a.pid
WHERE NOT l.granted;
# terminate a blocked session
SELECT pg_cancel_backend(pid); -- cancel query
SELECT pg_terminate_backend(pid); -- kill session
# deadlocks (PostgreSQL detects and resolves by killing one transaction)
-- Transaction A: UPDATE users WHERE id=1; UPDATE users WHERE id=2;
-- Transaction B: UPDATE users WHERE id=2; UPDATE users WHERE id=1;
-- -> ERROR: deadlock detectedMVCC とスナップショット分離
読み手が書き手を決してブロックせず、その逆もないのが、PostgreSQL の MVCC です — 各トランザクションがスナップショットを見ます。新しい行のバージョンを作るのが、UPDATE です。VACUUM が取り除くまで、古いバージョンが残ります。古いバージョンの後始末を防ぐのが、長期間実行されるトランザクションであり、肥大を引き起こします。トランザクションは短く保ちましょう!「idle in transaction」のセッションを監視しましょう — スナップショットを保持し後始末をブロックします。重大な問題が、トランザクション ID のラップアラウンドです(autovacuum が防ぎますが、autovacuum が苦戦していたら監視しましょう)。
# PostgreSQL uses MVCC (Multi-Version Concurrency Control)
# each transaction sees a consistent snapshot of data
# visible transactions
SELECT txid_current(); -- current transaction ID
SELECT txid_snapshot_xmin(txid_current_snapshot());
# see long-running transactions (can prevent vacuum)
SELECT
pid,
age(clock_timestamp(), xact_start) AS duration,
state,
query
FROM pg_stat_activity
WHERE state IN ('active', 'idle in transaction')
ORDER BY xact_start;
# long transactions block vacuum (old row versions can't be removed)
# check for this:
SELECT
pid,
age(txid_current(), backend_xmin) AS xmin_age
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY xmin_age DESC;
# vacuum and the transaction ID wraparound
# (autovacuum handles this, but monitor it!)
SELECT relname, last_autovacuum, last_autoanalyze, n_dead_tup
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000;ロックのパターン
行を悲観的にロックするのが、FOR UPDATE です — 重大な一貫性(送金)に使いましょう。低い競合には良いのが、楽観的ロッキング(version 列)です:ロックがなく、競合で再試行するだけです。job キューに完璧なのが、SKIP LOCKED です — 複数の worker がブロックせず異なるジョブを掴みます。ロックされていたら速く失敗するのが、NOWAIT です(「更新を試みるが待たない」に)。アプリケーションレベルの操作(移行、cron ジョブ)を調整するのが、advisory ロックです。高い競合には悲観的を、低い競合には楽観的を選びましょう。
# pessimistic locking (lock before work)
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- now safe to modify
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;
# optimistic locking (check version, retry on conflict)
-- uses a version column
UPDATE products SET stock = stock - 1, version = version + 1
WHERE id = 1 AND version = 5; -- 0 rows = conflict, retry
# SKIP LOCKED (process queue items without blocking)
SELECT * FROM job_queue
WHERE status = 'pending'
ORDER BY created_at
FOR UPDATE SKIP LOCKED
LIMIT 10;
-- grabs 10 jobs, skips locked ones (other workers don't wait)
# NOWAIT (fail immediately if locked)
SELECT * FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;
-- ERROR: could not obtain lock if row is locked
# named lock for coordination
SELECT pg_advisory_lock(hashtext('migrate_users'));
-- only one session can hold this lock
SELECT pg_advisory_unlock(hashtext('migrate_users'));ビューとマテリアライズドビュー
ビューの作成
テーブルのように振る舞う保存されたクエリが、ビューです。自動的に更新可能なのが(単一のテーブル、集約なし)、単純なビューです — 挿入/更新が基底テーブルへ通ります。行がビュー越しに見えなくなる挿入/更新を防ぐのが、WITH CHECK OPTION です。複雑なクエリの単純化、セキュリティの強制(列レベルのアクセス)、安定した API の提供に、ビューを使いましょう。依存オブジェクトを落とすのが、CASCADE です。データを保存せず(毎回クエリを実行します)、そののが、ビューです。
# simple view
CREATE VIEW active_users AS
SELECT id, username, email FROM users WHERE active = true;
# use it like a table
SELECT * FROM active_users WHERE username LIKE 'a%';
# view with computed columns
CREATE VIEW user_summary AS
SELECT
u.id,
u.username,
count(p.id) AS post_count,
max(p.created_at) AS last_post
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id, u.username;
# updatable view (simple views can be inserted/updated through)
CREATE VIEW user_emails AS
SELECT id, email FROM users;
INSERT INTO user_emails (id, email) VALUES (1, '[email protected]'); -- works!
# WITH CHECK OPTION (prevent inserts that wouldn't be visible)
CREATE VIEW active_users AS
SELECT * FROM users WHERE active = true
WITH CHECK OPTION;
-- INSERT with active=false fails
# modify or drop a view
ALTER VIEW active_users RENAME TO current_users;
DROP VIEW IF EXISTS active_users CASCADE;マテリアライズドビュー
クエリの結果を保存するのが、マテリアライズドビューです。クエリがはるかに速いが、refresh されるまで古いです。高コストな集約(ダッシュボード、レポート)に使いましょう。ビューをロックせず refresh するのが(読み手が動き続けます)、CONCURRENTLY ですが、一意のインデックスが要ります。pg_cron や外部ツールで refresh をスケジュールしましょう。リアルタイム風のデータには数分ごとに、歴史的分析には日次です。トレードオフが、新鮮さ対クエリ速度です — 大きいテーブルでの速い分析の鍵が、マテリアライズドビューです。
# create a materialized view (stores the result)
CREATE MATERIALIZED VIEW sales_summary AS
SELECT
date_trunc('day', created_at) AS day,
product_id,
count(*) AS order_count,
sum(amount) AS total_revenue
FROM orders
GROUP BY 1, 2
WITH DATA; -- or WITH NO DATA to populate later
# refresh (re-runs the query)
REFRESH MATERIALIZED VIEW sales_summary;
REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary; -- needs unique index
# create a unique index for concurrent refresh
CREATE UNIQUE INDEX idx_sales_summary_day_product
ON sales_summary(day, product_id);
# schedule refresh (use pg_cron extension or external scheduler)
-- pg_cron: SELECT cron.schedule('0 * * * *', 'REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary');
# drop
DROP MATERIALIZED VIEW IF EXISTS sales_summary;
# use it (like a regular table)
SELECT * FROM sales_summary WHERE day = '2025-01-01';セキュリティのビュー
強力なセキュリティの道具であるのが、ビューです。テーブルへのアクセスを付与せず、ユーザーが見るべき列/行だけを公開します。ビュー上に SELECT を付与し、基底テーブル上にはしません。行レベルのセキュリティのためには、ビューの代わりに、PostgreSQL の組み込みの RLS(Row Level Security)のポリシーを検討しましょう — より堅牢で、直接のテーブルアクセスとも機能します。ユーザーごとのフィルタリングを可能にするのが、セッション変数(current_setting)付きのビューです。アプリからスキーマの複雑さを隠す、安定した API 層を作るのに、ビューを使いましょう。
# column-level security (hide sensitive columns)
CREATE VIEW public_users AS
SELECT id, username, created_at FROM users;
-- password, email columns are hidden
# row-level security via view
CREATE VIEW user_posts AS
SELECT * FROM posts WHERE user_id = current_setting('app.current_user_id')::int;
-- set the user context per session
SET app.current_user_id = '42';
SELECT * FROM user_posts; -- only sees user 42's posts
# view with joins for simplified access
CREATE VIEW order_details AS
SELECT
o.id AS order_id,
o.created_at,
u.username,
p.name AS product_name,
oi.quantity,
oi.price
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id;
# grant access to view but not underlying tables
GRANT SELECT ON public_users TO app_readonly;
REVOKE SELECT ON users FROM app_readonly;ビューの管理
制限があるのが、CREATE OR REPLACE VIEW です — 末尾に列を加えられますが、取り除き・並べ替えられません。重大な変化には、落として再作成しましょう(しかし、まず依存を確認します)。他のビューに依存し得るのが、ビューであり、基底ビューの削除には CASCADE が要ります。定義の検査には、pg_views を。メンテナンススクリプトに便利なのが、すべてのマテリアライズドビューを refresh する DO ブロックです — しかし、可能な限り、ロックを避けるため CONCURRENTLY を。
# list views
SELECT viewname, definition FROM pg_views WHERE schemaname = 'public';
# get view definition
\d+ view_name -- in psql
# create or replace (limited — can't change columns)
CREATE OR REPLACE VIEW user_summary AS
SELECT id, username, email FROM users WHERE active = true;
-- can add columns at the end, but can't remove/reorder
# to change columns significantly, drop and recreate
DROP VIEW IF EXISTS user_summary;
CREATE VIEW user_summary AS
SELECT id, username FROM users WHERE active = true;
# view dependencies (what depends on this view)
SELECT dependee.relname AS view, depender.relname AS depends_on
FROM pg_depend d
JOIN pg_class dependee ON d.objid = dependee.oid
JOIN pg_class depender ON d.refobjid = depender.oid
WHERE dependee.relname = 'user_summary';
# refresh all materialized views
DO $$
DECLARE r RECORD;
BEGIN
FOR r IN SELECT matviewname FROM pg_matviews WHERE schemaname = 'public' LOOP
EXECUTE 'REFRESH MATERIALIZED VIEW ' || r.matviewname;
END LOOP;
END $$;再帰ビュー
再帰 CTE を包むのが(ツリー/グラフの走査に最良)、再帰ビュー(CREATE RECURSIVE VIEW)です。無限ループ(データの中の循環)を防ぐため、再帰部分に深さ/距離の上限を必ず含めましょう。走査パスを追跡・表示する簡単な方法が、(テキスト連結の)path 列です。非常に深い階層には、より良いパフォーマンスのために、closure テーブルか ltree 拡張を検討しましょう。便利だが、大きな dataset では遅くなり得るのが、再帰ビューです — 結合列にインデックスを加えましょう。
# recursive view for hierarchical data
CREATE RECURSIVE VIEW employee_tree(id, name, manager_id, level, path) AS
SELECT
id, name, manager_id, 0, name::text
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT
e.id, e.name, e.manager_id, et.level + 1,
et.path || ' > ' || e.name
FROM employees e
JOIN employee_tree et ON e.manager_id = et.id;
# query the tree
SELECT * FROM employee_tree ORDER BY path;
# find all subordinates (2 levels deep)
SELECT * FROM employee_tree
WHERE path LIKE 'CEO > Engineering > %' AND level <= 3;
# view for graph traversal (e.g., friend connections)
CREATE RECURSIVE VIEW friend_chain(person1, person2, distance, path) AS
SELECT person1, person2, 1, person1 || '->' || person2
FROM friendships
UNION
SELECT
fc.person1, f.person2, fc.distance + 1,
fc.path || '->' || f.person2
FROM friend_chain fc
JOIN friendships f ON fc.person2 = f.person1
WHERE fc.distance < 6; -- limit depth to prevent infinite loops関数と演算子
文字列関数
包括的な文字列関数を PostgreSQL が持ちます。部分文字列を見つけるのが、position() と strpos() です。SQL 標準なのが、FROM/FOR 付きの substring() で、よりコンパクトなのが、substr() です。区切りデータに便利なのが、split_part() です。各単語を大文字にするのが、initcap() です。文字列を固定幅へパディングするのが(整形に有用)、lpad/rpad です。文字列と配列の間で変換するのが、string_to_array/array_to_string です。パターンマッチングのほとんどのニーズを、LIKE/ILIKE(ワイルドカード)、~(正規表現)、SIMILAR TO(SQL パターン)がカバーします。
# length and position
SELECT length('hello'), char_length('hello'), octet_length('hello');
SELECT position('lo' in 'hello'); -- 4
SELECT strpos('hello', 'lo'); -- 4
# substring and slicing
SELECT substring('hello' from 2 for 3); -- 'ell'
SELECT substring('hello' from 2); -- 'ello'
SELECT substr('hello', 2, 3); -- 'ell'
SELECT left('hello', 3); -- 'hel'
SELECT right('hello', 3); -- 'llo'
# case and trim
SELECT upper('hello'), lower('HELLO'), initcap('hello world');
SELECT trim(' hello '), ltrim('xxhello', 'x'), rtrim('helloxx', 'x');
SELECT btrim('xxhelloxx', 'x'); -- trim both sides
# split and join
SELECT split_part('a,b,c', ',', 2); -- 'b' (1-indexed)
SELECT string_to_array('a,b,c', ','); -- {a,b,c}
SELECT array_to_string(ARRAY['a','b','c'], ', '); -- 'a, b, c'
# replace and pad
SELECT replace('hello', 'l', 'L'); -- 'heLLo'
SELECT lpad('5', 3, '0'); -- '005'
SELECT rpad('5', 3, '0'); -- '500'
SELECT repeat('ab', 3); -- 'ababab'
SELECT reverse('hello'); -- 'olleh'日付/時刻関数
等価なのが(トランザクション開始時刻)、now() と current_timestamp です。コンポーネントを取得するのが、EXTRACT/date_part です。単位へ切り下げるのが、date_trunc で — 時間バケット化データには不可欠です。インターバルを返すのが(人間可読)、age() です。整形/パースを扱うのが、to_char/to_date/to_timestamp です。時系列データのギャップの充填には、日付付きの generate_series が信じられないほど有用です(ゼロカウントの日を示すため、LEFT JOIN しましょう)。timestamptz を必ず使い、セッションのタイムゾーンを尊重するのが、日付の算術です。
# current date/time
SELECT now(), current_timestamp, transaction_timestamp();
SELECT current_date, current_time;
# extract components
SELECT EXTRACT(YEAR FROM now()), EXTRACT(MONTH FROM now());
SELECT date_part('dow', now()); -- day of week (0=Sunday)
SELECT date_part('epoch', now()); -- Unix timestamp
# date arithmetic
SELECT now() + interval '1 day';
SELECT now() - interval '2 hours';
SELECT age('2025-01-01'); -- interval since date
SELECT age('2025-01-01', '2024-01-01'); -- interval between dates
SELECT date '2025-01-15' - date '2025-01-01'; -- 14 (integer days)
# truncation and rounding
SELECT date_trunc('month', now()); -- first day of month, 00:00
SELECT date_trunc('hour', now()); -- current hour, 00 minutes
# formatting
SELECT to_char(now(), 'YYYY-MM-DD HH:MI:SS');
SELECT to_char(now(), 'Day, DD Mon YYYY');
SELECT to_char(12345.678, 'FM999,999.00');
SELECT to_date('2025/01/15', 'YYYY/MM/DD');
SELECT to_timestamp(1700000000); -- Unix epoch
# generate series (date ranges)
SELECT generate_series(
'2025-01-01'::date,
'2025-01-31'::date,
'1 day'::interval
);数値と数学の関数
(double ではなく)numeric 上で機能するのが、2 引数付きの round() です — 先に cast しましょう。ラジアンを使うのが、三角関数で(変換には radians()/degrees() を)。0〜1 を返すのが、random() で、範囲のため数学と組み合わせましょう。PostgreSQL の主力が、generate_series です。シーケンス、日付範囲、ギャップの充填のために、その場で行を生成します。便利なのが、gcd/lcm(13+)です。財務の計算には、(正確な)numeric を必ず使いましょう — double 上の round のような浮動小数点関数が、精度の問題を持ち得ます。
# rounding
SELECT round(3.14159, 2); -- 3.14
SELECT ceil(3.1), ceiling(3.1); -- 4
SELECT floor(3.9); -- 3
SELECT trunc(3.14159, 2); -- 3.14 (truncate, no rounding)
# power and roots
SELECT power(2, 10); -- 1024
SELECT sqrt(16); -- 4
SELECT cbrt(27); -- 3
SELECT exp(1); -- 2.718... (e^x)
SELECT ln(10), log(100); -- natural log, base-10 log
# trigonometry (radians!)
SELECT sin(radians(90)); -- 1 (convert degrees to radians)
SELECT degrees(1.5708); -- ~90
SELECT pi(); -- 3.14159...
# random
SELECT random(); -- 0 to 1
SELECT floor(random() * 100)::int; -- 0 to 99
SELECT setseed(0.5); -- seed for reproducibility
# sequences and ranges
SELECT generate_series(1, 10);
SELECT generate_series(1, 10, 2); -- 1, 3, 5, 7, 9
SELECT generate_series(0, 1, 0.1); -- 0.0, 0.1, ..., 1.0
# absolute, sign, gcd
SELECT abs(-5), sign(-5); -- 5, -1
SELECT gcd(12, 8); -- 4 (PG 13+)
SELECT lcm(12, 8); -- 24 (PG 13+)条件と NULL の関数
最初の非 NULL 値を返すのが、COALESCE です — デフォルト化には不可欠です。値が一致すれば NULL を返すのが、NULLIF で、ゼロ除算の回避には CASE よりきれいです。値を比較するのが、GREATEST/LEAST です(GREATEST では NULL が伝播しますが、LEAST では常にではありません)。一般的な条件式が、CASE です。PG のショートカットが、ISNULL/NOTNULL です。辞書式に比較するのが、行比較(ROW(a,b) < ROW(c,d))です — 複合キーのページネーションに有用です。NULL の挙動の理解(NULL = NULL が真でなく NULL)が、PostgreSQL では決定的に重要です。
# COALESCE (return first non-NULL)
SELECT COALESCE(nickname, username, email, 'anonymous') FROM users;
# NULLIF (return NULL if two values are equal)
SELECT NULLIF(status, '') FROM users; -- '' becomes NULL
-- useful to avoid division by zero:
SELECT total / NULLIF(count, 0) FROM stats; -- NULL instead of error
# GREATEST / LEAST
SELECT GREATEST(1, 2, 3); -- 3
SELECT LEAST(NULL, 1, 2); -- NULL (NULLs propagate)
SELECT GREATEST(a, b, c) FROM numbers;
# CASE expressions
SELECT
CASE
WHEN age < 18 THEN 'minor'
WHEN age >= 65 THEN 'senior'
ELSE 'adult'
END AS age_group
FROM users;
# ISNULL / NOTNULL (PostgreSQL shortcuts)
SELECT * FROM users WHERE email ISNULL; -- IS NULL
SELECT * FROM users WHERE email NOTNULL; -- IS NOT NULL
# row comparison
SELECT ROW(1, 2) < ROW(1, 3); -- true (compares column by column)配列と JSON の関数
データベース内の強力なデータ操作を可能にするのが、配列の関数です:行への配列の展開が(結合に最適)、unnest で、逆が array_agg です。JSONB の演算子:jsonb を返すのが、-> と #> で、テキストを返すのが、->> と #>> です。最重要の JSONB 演算子であり、GIN インデックスを使うのが、@>(包含)です。キーの存在を確認するのが、? です。列から JSON を構築するのが、jsonb_build_object です。JSON を修正するのが、jsonb_set/||/- です。一級のデータ型にするのが、JSONB に対するこれらの関数です — 柔軟でありながらクエリ可能です。