エラーの内容
JOINクエリを実行すると、PostgreSQLが即座に止まります：
ERROR: column reference "id" is ambiguous
LINE 1: SELECT id, name FROM orders JOIN users ON orders.user_id = u...
メッセージに表示されるカラム名は created_at、status、name など何でもあり得ます。しかし根本原因は常に同じです：2つ以上のテーブルがまったく同じ名前のカラムを持っており、PostgreSQLはどちらを意図したのかを推測しません。拒否するのです。これは実は良いことです——静かに誤ったデータを返されるよりも、大きなエラーが出る方がはるかにましです。
なぜ発生するのか
PostgreSQLは orders と users の両方に id を見つけます。どちらを選ぶべきか判断する方法がないため、古いデータや誤ったデータを静かに返すのではなく、曖昧さエラーをスローします。
実際によく起きる3つのシナリオがあります：
- 単一テーブルのクエリをJOINにリファクタリングしたが、
SELECTのカラム名を修飾し忘れた。 - 誰かが別のテーブルに
created_atカラムを追加した——コードを変更していないのに既存のクエリが壊れた。 - 動作しているクエリをコピーしてJOINを追加したが、
SELECTリストを更新しなかった。
修正1：曖昧なカラムにテーブル名を付けて修飾する
各カラムの前に所属するテーブル名をプレフィックスとして付けます。シンプルで明示的、曖昧さはゼロです。
-- BEFORE (fails)
SELECT id, name, email
FROM orders
JOIN users ON orders.user_id = users.id;
-- AFTER (works)
SELECT orders.id, users.name, users.email
FROM orders
JOIN users ON orders.user_id = users.id;
SELECT だけで止まらないでください。WHERE、ORDER BY、GROUP BY、HAVING も確認しましょう。複数のテーブルに存在する修飾されていないカラムは、同じエラーを引き起こします。
修正2：テーブルエイリアスを使ってクエリを読みやすくする
いたるところに very_long_table_name.column_name と書くのはすぐに見苦しくなります。短いエイリアスでそれを解消できます。
SELECT o.id AS order_id,
u.id AS user_id,
u.name AS user_name,
o.created_at
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'pending'
ORDER BY o.created_at DESC;
エイリアスはテーブル名の直後に宣言します——orders o——そして o. と u. をどこでも使います。3つ目のテーブルが加わっても、クエリはスキャンしやすくメンテナンスしやすくなります。
修正3：SELECTリストでカラムをリネームする
競合するカラムの両方が必要な場合もあります——たとえば orders.id と users.id の両方。後続の処理で結果セットが分かりやすくなるよう、それぞれに別名のエイリアスを付けましょう。
SELECT o.id AS order_id,
u.id AS user_id,
u.email,
o.total
FROM orders o
JOIN users u ON o.user_id = u.id;
エイリアスを省略すると、ORMやアプリケーションは id という名前のカラムを2つ受け取ります。ほとんどのフレームワークはどちらか一方を静かに削除します。どちらが削除されるかはドライバーによって異なります。これは本番環境で表面化するバグです。
修正4：SELECT * を明示的なカラムリストに置き換える
JOIN に対して SELECT * を使うと、すべてのテーブルからすべてのカラムが取得されます——重複するものもすべて含めて。PostgreSQLは一部のコンテキストではエラーを出さずに許可しますが、結果セットには id、created_at などの名前のカラムが複数含まれることになります。
-- Avoid this with JOINs
SELECT * FROM orders JOIN users ON orders.user_id = users.id;
-- Spell out exactly what you need
SELECT o.id, o.total, o.status,
u.name, u.email
FROM orders o
JOIN users u ON o.user_id = u.id;
明示的なカラムリストは、誰かがテーブルスキーマを変更した際も保護してくれます——APIレスポンスに新しいカラムが誤って含まれることを防げます。
修正5：サブクエリまたはCTE内でカラムに事前エイリアスを付ける
外側のクエリを変更できない場合はどうでしょう？ビューやレガシーストアドプロシージャの内部にある場合などです。テーブルのいずれかをサブクエリでラップし、競合するカラムをそこでリネームします。
SELECT o.id, o.total, u_info.user_id, u_info.user_name
FROM orders o
JOIN (
SELECT id AS user_id, name AS user_name, email
FROM users
) u_info ON o.user_id = u_info.user_id;
より大きなクエリでは、CTEの方がすっきりと読めます：
WITH user_info AS (
SELECT id AS user_id, name AS user_name, email
FROM users
)
SELECT o.id, o.total, u.user_id, u.user_name
FROM orders o
JOIN user_info u ON o.user_id = u.user_id;
確認方法
クエリを再実行します。正常な出力はこのようになります：
order_id | user_id | user_name | total
----------+---------+-----------+-------
1 | 42 | Alice | 99.00
2 | 17 | Bob | 45.50
(2 rows)
psqlで長いクエリをデバッグしていますか？失敗の直後に \errverbose を実行してください。完全なエラーコンテキスト——曖昧な参照の正確な行番号と文字位置——が表示されます。50行のSQLを目で追うよりはるかに速いです。
\errverbose
予防策
- **2回目のエラーが出てからではなく、最初のJOINからカラムを修飾する。**早いうちに習慣をつけましょう——コストはゼロで、デバッグの手間を省けます。
- **JOINクエリで
SELECT *を禁止する。**コードが実際に使用するカラムだけを列挙します。 - **同名カラムを選択する場合は両側にエイリアスを付ける。**1つの結果セットに
idという名前のカラムが2つあると、ほとんどのORMは静かに壊れます。 - **
ALTER TABLEの前にカラム名を確認する。**新しいカラムが追加されると、同名のカラムを持つテーブルに対してJOINしている既存クエリが壊れる可能性があります——マイグレーションを実行する前にクエリをgrepで確認しましょう。 - CIにSQLリンターを使用する。squawk や pgFormatter などのツールは、マルチテーブルクエリ内の修飾されていないカラムを本番環境に到達する前にフラグを立てます。