PostgreSQL 辞典

psql メタコマンド\ で始まる psql の命令Lv1 で覚える

\dt  \d 表  \di  \dv  \ds  \df
\x  \l  \dn  \du  \conninfo  \?  \q

SQL ではなく psql 自身の命令で、; は不要です。\d 表 で列の型・制約・インデックスを確かめられます。

\dt
\d orders

SERIAL / IDENTITY自動で番号を振る列Lv3 で覚える

id SERIAL PRIMARY KEY
id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY

INSERT で列を省くと 1, 2, 3 … と自動で番号が入ります。IDENTITY は標準 SQL の書き方です。

CREATE TABLE notes (id SERIAL PRIMARY KEY, body TEXT);

シーケンス番号を出す仕組みLv3 で覚える

CREATE SEQUENCE 名前 [START WITH n] [INCREMENT BY n];
nextval('名前')  currval('名前')  setval('名前', n)
名前.NEXTVAL / 名前.CURRVAL（Oracle）

呼ぶたびに次の番号を返します。SERIAL や IDENTITY も中ではシーケンスを使っています。

SELECT nextval('order_no');

RETURNING変えた行を返すLv4 で覚える

INSERT … RETURNING 列;
UPDATE … RETURNING *;
DELETE … RETURNING *;

自動で振られた ID や、更新後の値をその場で受け取れます（PostgreSQL）。

INSERT INTO notes (body) VALUES ('hello') RETURNING id;

ON CONFLICTあれば更新・なければ追加Lv5 で覚える

INSERT … ON CONFLICT (列) DO NOTHING;
INSERT … ON CONFLICT (列) DO UPDATE SET 列 = EXCLUDED.列;

一意制約にぶつかったときの動きを決めます。EXCLUDED は入れようとした値です（PostgreSQL）。

INSERT INTO stock (item_id, qty) VALUES (1, 5) ON CONFLICT (item_id) DO UPDATE SET qty = stock.qty + EXCLUDED.qty;

string_agg文字列をつなげて集計Lv6 で覚える

string_agg(列, '区切り' [ORDER BY 列])

グループの値を 1 つの文字列にまとめます（PostgreSQL）。

SELECT dept, string_agg(name, ', ' ORDER BY name) FROM staff GROUP BY dept;

FILTER条件付きで集計Lv6 で覚える

集計関数(…) FILTER (WHERE 条件)

SUM(CASE …) の代わりに、集計ごとに条件をつけられます（PostgreSQL）。

SELECT COUNT(*) FILTER (WHERE status = 'done') FROM tasks;

DISTINCT ONグループごとの先頭の 1 行Lv7 で覚える

SELECT DISTINCT ON (列) … ORDER BY 列, 並べ方;

ORDER BY で並べたときの、各グループの最初の行だけを残します（PostgreSQL）。

SELECT DISTINCT ON (customer_id) * FROM orders ORDER BY customer_id, ordered_at DESC;

generate_series連続した値の行を作るLv8 で覚える

generate_series(開始, 終わり[, 増分])
generate_series('2024-04-01'::date, '2024-04-30', '1 day')

数や日付の連続を作ります。LEFT JOIN と組み合わせて「データのない日」も 0 で出せます（PostgreSQL）。

SELECT d::date FROM generate_series('2024-04-01'::date, '2024-04-07', '1 day') AS d;

配列値の並びを 1 列にLv11 で覚える

列 text[]  ARRAY['a', 'b']
値 = ANY(配列)  配列 @> ARRAY[…]
unnest(配列)  array_agg(列)  array_length(配列, 1)

タグのような複数の値を 1 列に入れられます（PostgreSQL）。unnest で行に、array_agg で配列にまとめます。

SELECT * FROM posts WHERE 'sql' = ANY(tags);

JSONBJSON を入れて検索するLv12 で覚える

列 -> 'キー'（JSON のまま）  列 ->> 'キー'（文字列で）
列 @> '{"キー": 値}'  列 ? 'キー'
jsonb_build_object  jsonb_array_elements  jsonb_set

形の決まっていないデータを 1 列に入れて、キーで取り出したり検索したりできます（PostgreSQL）。

SELECT data->>'name' FROM events WHERE data @> '{"type": "login"}';

LATERAL行ごとに副問い合わせLv14 で覚える

FROM a JOIN LATERAL (SELECT … WHERE … = a.列 LIMIT n) b ON true

左の表の行ごとに、右の副問い合わせを実行します。グループごとの上位 n 件に便利です。

関数・PL/pgSQL処理に名前をつけて再利用Lv17 で覚える

CREATE FUNCTION 名前(引数 型) RETURNS 型 LANGUAGE sql AS $$ … $$;
CREATE FUNCTION … LANGUAGE plpgsql AS $$ DECLARE … BEGIN … END $$;
DO $$ BEGIN RAISE NOTICE '%', 値; END $$;

PL/pgSQL では変数・IF・ループが使えます。$$ … $$ の中に本体を書きます。

CREATE FUNCTION tax(p NUMERIC) RETURNS NUMERIC LANGUAGE sql AS $$ SELECT ROUND(p * 1.1) $$;

トリガー変更のたびに自動で動くLv18 で覚える

PostgreSQL: CREATE FUNCTION f() RETURNS trigger … ; CREATE TRIGGER t BEFORE UPDATE ON 表 FOR EACH ROW EXECUTE FUNCTION f();
Oracle: CREATE OR REPLACE TRIGGER t BEFORE INSERT ON 表 FOR EACH ROW BEGIN … END; /

INSERT・UPDATE・DELETE の前後に処理を入れます。NEW は新しい行、OLD は古い行（Oracle は :NEW / :OLD）。

EXPLAIN実行計画を見るLv19 で覚える

EXPLAIN SELECT …;

どうやってデータを探すか（Seq Scan で全件か、Index Scan でインデックスか）が分かります。

EXPLAIN SELECT * FROM orders WHERE customer_id = 3;