なぜこのエラーが発生するのか
昨日まで完璧に動作していたクエリが、今日突然クラッシュすることがあります。PostgreSQLは単一の値（スカラー）を期待していますが、サブクエリが2行以上のリストを返したことを伝えています。これは通常、データ内の1:1の関係が予期せず1:N（一対多）の関係に変わったときに発生します。
根本原因の特定
SQLにおいて、「スカラーサブクエリ」は正確に1列かつ1行を返さなければなりません。返される行が0行の場合、PostgreSQLはその結果を NULL として扱います。しかし、2行以上返されると、エンジンはどの値を使用すべきか判断できず、実行を停止します。これは通常、以下の3つの場所で見られます：
- 関連する値を取得するための
SELECTリスト内。 =、<、>などの比較演算子の右側。- カラムに新しい値を割り当てる際の
UPDATE ... SET句内。
ステップバイステップの修正方法
1. 問題のあるデータを特定する
コードを変更する前に、競合の原因となっている行を見つけます。例えば、プロファイルIDに基づいてメールアドレスを取得している場合、重複がないか確認します。以下のクエリを使用して、複数のレコードに関連付けられているIDを検索します：
SELECT profile_id, COUNT(*)
FROM users
GROUP BY profile_id
HAVING COUNT(*) > 1;
もし結果が返ってくるなら、データの整合性が崩れています。同じ profile_id を共有するユーザーが2人存在し、それがロジックを壊している可能性があります。
2. クイックフィックスとして LIMIT 1 を使用する
最新のレコードのみが必要な場合は、LIMIT 1 を追加します。これは、正確な一致よりもクエリを正常に完了させることが重要なレポートなどでよく使われる応急処置です。結果の一貫性を保つために、必ず ORDER BY と組み合わせて使用してください。
SELECT
id,
(SELECT email FROM users
WHERE profile_id = profiles.id
ORDER BY created_at DESC LIMIT 1) as user_email
FROM profiles;
3. '=' を 'IN' または 'ANY' に置き換える
サブクエリが WHERE 句にある場合、多くの場合、演算子を変更するだけで修正できます。= を使うと、PostgreSQLは1つの値を強制的に探します。IN に切り替えることで、値のリストとの照合を許容することをデータベースに伝えます。
エラーが発生するクエリ:
SELECT * FROM orders
WHERE user_id = (SELECT id FROM users WHERE status = '有効');
修正後のクエリ:
SELECT * FROM orders
WHERE user_id IN (SELECT id FROM users WHERE status = '有効');
4. 結果を集約する
すべてのデータが必要だが、複数行に分かれてほしくない場合があります。ユーザーが3つのメールアドレスを持っている場合、string_agg を使用してそれらを1つのカンマ区切り文字列に結合できます。これにより、データを保持したまま「1行」の要件を満たすことができます。
SELECT
id,
(SELECT string_agg(email, ', ') FROM users WHERE profile_id = profiles.id) as all_emails
FROM profiles;
5. JOIN へのリファクタリング（パフォーマンスに最適）
SELECT リスト内のサブクエリは、結果セットの各行に対して実行されるため、低速になることがよくあります。100,000件のプロファイルがある場合、PostgreSQLは100,000回のサブクエリを実行する可能性があります。LEFT JOIN は大幅に効率的であり、クラッシュする代わりに、複数の一致がある場合は行を追加することで対応します。
SELECT
p.id,
u.email
FROM profiles p
LEFT JOIN users u ON u.profile_id = p.id;
検証手順
修正を適用した後、サブクエリのロジックが重複を生成しなくなったことを確認します。再度チェック用のクエリを実行してください：
SELECT profile_id FROM users GROUP BY profile_id HAVING COUNT(*) > 1;
0行が返されれば、1:1の関係が復元されています。JOIN や LIMIT のアプローチを使用した場合は、メインクエリを実行し、行数が期待通りであることを確認してください。重複が依然として存在する場合、JOIN を使うと結果セットの総行数が増える可能性があります。
プロのヒント
- EXISTS を優先する： レコードが存在するかどうかを確認するだけでよい場合は、
WHERE EXISTS (SELECT 1 FROM ...)を使用してください。これはスカラーサブクエリよりも高速で、「more than one row」エラーをスローすることもありません。 - Lateral Join： 「トップ1」の関連レコードが必要だが、サブクエリよりも高いパフォーマンスを求める複雑なロジックの場合は、
LEFT JOIN LATERALを使用します。これにより、親テーブルのカラムにアクセスしながら、各行に対してサブクエリを実行できます。 - データベース制約： 予防は深夜のバグ修正に勝ります。カラムに1つの一致しか存在しないはずの場合は、
UNIQUE制約を追加してください。これにより、重要な読み取り操作中ではなく、データ入力時にエラーを強制的に発生させることができます。