PostgreSQL『more than one row returned by a subquery used as an expression』の原因と対処

ERROR:  more than one row returned by a subquery used as an expression
-- SQLSTATE 21000（cardinality violation）

□ = (SELECT ...) / <> (SELECT ...) / col = (SELECT col FROM ...) で複数行が返る
□ SELECT 句の中に (SELECT ...) があり、1行に決めきれない
□ INSERT/UPDATE の値式に (SELECT ...) を使い、複数行が返る
□ CHECK/生成列などの式でも同様に複数行
□ 相関副問い合わせの結合キーがユニークでない（子が1対多）

-- 失敗したSQLの“副問い合わせ部分”だけを単独で実行し、件数を確認
SELECT COUNT(*) FROM ( <問題のサブクエリ> ) s;

-- psql なら直前エラーの詳報
\errverbose
\set VERBOSITY verbose

-- 典型：相関キーでの重複を発見
SELECT key, COUNT(*) c
FROM child
GROUP BY key HAVING COUNT(*) > 1;

DROP TABLE IF EXISTS users, orders;
CREATE TABLE users (id int PRIMARY KEY, name text);
CREATE TABLE orders (id int PRIMARY KEY, user_id int, amount int);
INSERT INTO users VALUES (1,'alice'), (2,'bob');
INSERT INTO orders VALUES (10,1,100), (11,1,200), (12,2,300);

-- ユーザー毎の最新注文金額を“式のサブクエリ”で取得しようとして…
SELECT
  u.id,
  (SELECT amount FROM orders o WHERE o.user_id = u.id) AS any_amount
FROM users u;
-- エラー：alice(1)の行で2行返るため

・「どれか1件でよい」→ ORDER BY ... LIMIT 1 / DISTINCT ON / ROW_NUMBER()=1
・「重複があるが、代表値を決めたい」→ MIN/MAX/AVG/STRING_AGG/JSON_AGG
・「複数可」→ IN / EXISTS / JOIN（1対多として展開）
・「全部まとめて1値」→ ARRAY(SELECT ...) / jsonb_agg(SELECT ...)
・「本来は1件のはず」→ キーに UNIQUE 制約を付けてデータ不整合を排除

-- NG：単一値比較
SELECT * FROM users u
WHERE u.id = (SELECT user_id FROM orders WHERE amount >= 200);

-- OK：複数行を前提に
SELECT * FROM users u
WHERE u.id IN (SELECT user_id FROM orders WHERE amount >= 200);

-- EXISTS 版（関連行の存在を問う）
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.amount >= 200);

-- JOIN で明示的に1対多を展開
SELECT DISTINCT u.*
FROM users u JOIN orders o ON o.user_id = u.id
WHERE o.amount >= 200;

-- 1) ORDER BY ... LIMIT 1（最新/最小/最大などのルールを明示）
SELECT u.id,
       (SELECT o.amount
        FROM orders o
        WHERE o.user_id = u.id
        ORDER BY o.id DESC   -- 例：新しい順
        LIMIT 1) AS latest_amount
FROM users u;

-- 2) DISTINCT ON（グループ内で優先順位を決めて1行）
SELECT DISTINCT ON (o.user_id) o.user_id, o.amount
FROM orders o
ORDER BY o.user_id, o.id DESC;

-- 3) 窓関数 ROW_NUMBER（後続で rn=1 を選ぶ）
WITH ranked AS (
  SELECT o.*, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY id DESC) rn
  FROM orders o
)
SELECT u.id,
       (SELECT amount FROM ranked r WHERE r.user_id=u.id AND r.rn=1) AS latest_amount
FROM users u;

-- 代表値を決める
SELECT u.id,
       (SELECT MAX(o.amount) FROM orders o WHERE o.user_id=u.id) AS max_amount
FROM users u;

-- 文字列に連結（API応答向け）
SELECT u.id,
       (SELECT STRING_AGG(o.amount::text, ',' ORDER BY o.id)
        FROM orders o WHERE o.user_id=u.id) AS amounts
FROM users u;

-- JSON/配列で受ける
SELECT u.id,
       (SELECT jsonb_agg(o.amount ORDER BY o.id)
        FROM orders o WHERE o.user_id=u.id) AS amounts_json
FROM users u;

-- NG：SET col = (SELECT ...) が複数行
UPDATE users u
SET name = (SELECT 'VIP' FROM orders o WHERE o.user_id=u.id AND o.amount>=200);

-- OK：FROM で結合し、代表行か集約にする
UPDATE users u
SET name = 'VIP'
FROM (
  SELECT DISTINCT user_id FROM orders WHERE amount>=200
) o
WHERE u.id = o.user_id;

-- 代表値を使う（例：最新金額で更新）
UPDATE users u
SET name = 'VIP ' || latest.amount
FROM (
  SELECT DISTINCT ON (user_id) user_id, amount
  FROM orders ORDER BY user_id, id DESC
) latest
WHERE u.id = latest.user_id;

-- 「user→profile は常に1:1のはず」ならスキーマで保証
CREATE TABLE profiles(user_id int PRIMARY KEY, nick text);
-- あるいは
CREATE UNIQUE INDEX ux_profile_user ON profiles(user_id);

-- これにより (SELECT nick FROM profiles WHERE user_id=u.id) は1行に保たれる

-- 任意一致：= ANY(ARRAY(SELECT ...)) で“式のまま”複数値に拡張
SELECT * FROM users
WHERE id = ANY( ARRAY(SELECT user_id FROM orders WHERE amount>=200) );

-- 1) 2件以上ならNULLに落とす（あるいはデフォルト）
SELECT CASE
         WHEN (SELECT COUNT(*) FROM orders o WHERE o.user_id=u.id) = 1
         THEN (SELECT amount FROM orders o WHERE o.user_id=u.id)
       END AS only_amount
FROM users u;

-- 2) テスト/監視用：LIMIT 2 で「2件目があるか」を探知
SELECT u.id,
       (SELECT COUNT(*) FROM (SELECT 1 FROM orders o WHERE o.user_id=u.id LIMIT 2) s) AS c2
FROM users u
WHERE /* c2 = 2 の行が“要修正データ” */;

× col = (SELECT x FROM t WHERE ...)       -- 複数行の可能性
○ col IN (SELECT x FROM t WHERE ...)      -- 複数行を許容
○ EXISTS (SELECT 1 FROM t WHERE ...)      -- 存在判定
○ col = (SELECT ... ORDER BY ... LIMIT 1) -- 代表1件を決める
○ (SELECT MAX(x) FROM t WHERE ...)        -- 集約で1値化
○ ARRAY(SELECT ...) / jsonb_agg(...)      -- まとめて1値（配列/JSON）

-- 目的：ユーザーごとに「最新注文金額」を1値で取得

-- NG（エラー）
SELECT u.id,
       (SELECT amount FROM orders o WHERE o.user_id=u.id) AS latest_amount
FROM users u;

-- OK1：ORDER BY + LIMIT 1
SELECT u.id,
       (SELECT o.amount FROM orders o WHERE o.user_id=u.id ORDER BY o.id DESC LIMIT 1) AS latest_amount
FROM users u;

-- OK2：DISTINCT ON で事前に最新行を作ってJOIN
WITH latest AS (
  SELECT DISTINCT ON (user_id) user_id, amount
  FROM orders ORDER BY user_id, id DESC
)
SELECT u.id, l.amount
FROM users u LEFT JOIN latest l ON l.user_id=u.id;

-- OK3：ROW_NUMBER でランク付け
WITH ranked AS (
  SELECT o.*, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY id DESC) rn
  FROM orders o
)
SELECT u.id, r.amount
FROM users u LEFT JOIN ranked r ON r.user_id=u.id AND r.rn=1;

-- OK4：MAX で代表値（“最新＝id最大”が妥当な定義なら）
SELECT u.id,
       (SELECT MAX(o.amount) FROM orders o WHERE o.user_id=u.id) AS latest_amount
FROM users u;

□ 副問い合わせが“必ず1行”である保証があるか（データ/制約で担保）
□ 1件の定義（最新/最大/最小/優先度）を明文化し ORDER BY / 集約で表現
□ 複数行が正なら IN/EXISTS/JOIN を用いる
□ まとめて返すなら ARRAY/jsonb_agg/STRING_AGG で1値化
□ UPDATE/DELETE は FROM/JOIN で多行を自然に扱う形に
□ 相関キーがユニークになるよう UNIQUE 制約/インデックスを整備