【保存版】PostgreSQLの意外と知られていない便利関数10選

✅ はじめに

PostgreSQLは高機能なRDBMSとして知られていますが、実は「こんな便利な関数があったのか！」というレベルで、強力かつ実務的な関数が多く存在します。
この記事では、標準SQLにはないPostgreSQLならではの便利関数の中から、実務・分析・開発の現場で「これ知ってたら助かった！」というものを厳選して紹介します。

1. generate_series()｜連番や日付生成の最強関数

SELECT generate_series(1, 10);
SELECT generate_series('2024-01-01'::date, '2024-01-10', '1 day');

・数値だけでなく日付や時間にも対応し、データが存在しない日付の補完や、グラフ用の軸生成に超便利。

2. unnest()｜配列を1行ずつに展開する

SELECT unnest(ARRAY['A', 'B', 'C']);

・テーブル内の配列型カラムを「1要素1行」に展開できる。前処理や正規化に活躍。

3. string_agg()｜行を文字列として連結する

SELECT string_agg(名前, ', ') FROM 社員 WHERE 部署 = '営業';

・グループごとの「カンマ区切り表示」や、1行にまとめて一覧化したいときに最適。

4. age()｜日付の差を年/月/日で返す

SELECT age(current_date, '1990-01-01');
-- → 34 years 4 mons 13 days

・ interval よりも人間が読んで理解しやすいフォーマットで差分を表示。

5. jsonb_each_text()｜JSONBを正規化して扱う

SELECT * FROM jsonb_each_text('{"name": "Taro", "age": "30"}'::jsonb);

・ JSONB形式のカラムをキーと値に展開して扱えるため、NoSQL的データの正規化処理にも使える。

6. regexp_replace()｜正規表現で文字列置換

SELECT regexp_replace('123-456-789', '-', '', 'g');
-- → '123456789'

・電話番号や住所データの整形など、SQL内で正規表現を使ったクリーニングが可能。

7. date_trunc()｜日付の切り捨て（グルーピング向け）

SELECT date_trunc('month', current_timestamp);

・「月ごと」「週ごと」など、日付の丸め処理に。GROUP BYと合わせて集計に最適。

8. array_agg()｜行を配列にまとめる

SELECT array_agg(商品名) FROM 注文 WHERE 顧客ID = 1;

・逆の unnest() と対になる関数。1人の顧客に複数の注文があるときに配列でまとめたいときに。

9. width_bucket()｜数値を範囲（ビン）に分類

SELECT width_bucket(得点, 0, 100, 5);

・ヒストグラムやレポートで「帯域ごとの件数」を出したいときに便利。

10. setseed() + random()｜再現性のある乱数生成

SELECT setseed(0.42);
SELECT random(); -- 0〜1のランダム値

・テストデータの生成やサンプル抽出など、制御可能な乱数生成が可能。

■ まとめ：PostgreSQLは“関数の宝庫”

分類	関数名	用途
データ生成	generate_series()	連番・日付生成
配列操作	unnest(), array_agg()	行⇔配列の相互変換
文字列処理	string_agg(), regexp_replace()	整形・連結・置換
日付処理	date_trunc(), age()	丸め・差分表示
JSON操作	jsonb_each_text()	JSONを正規化
数値分類	width_bucket()	スコア帯の分類
乱数生成	random() + setseed()	ランダム値制御

PostgreSQLは、ちょっとした前処理や集計をSQLレベルで済ませられる強力なツールです。
「アプリで処理してたこと、SQLだけで完結できるかも？」と思ったら、ぜひこれらの関数を試してみてください。

■ 関連キーワード

PostgreSQL 便利関数一覧
PostgreSQL generate_series 使い方
PostgreSQL string_agg グループ化
PostgreSQL jsonb 展開
SQL 日付グルーピング
PostgreSQL 正規表現 replace
PostgreSQL ヒストグラム width_bucket

OK-PC