PostgreSQL「column reference is ambiguous」の原因と対処

-- サンプルデータ
CREATE TABLE users (id bigint PRIMARY KEY, name text);
CREATE TABLE orders (
  id bigint PRIMARY KEY,
  user_id bigint NOT NULL REFERENCES users(id),
  name text,
  created_at timestamp NOT NULL
);

-- NG: id と name が両テーブルにあり未修飾
SELECT id, name
FROM users u
JOIN orders o ON o.user_id = u.id;
-- ERROR: column reference "id" is ambiguous
-- ERROR: column reference "name" is ambiguous

-- OK: 常に修飾する
SELECT u.id, u.name
FROM users u
JOIN orders o ON o.user_id = u.id;

-- ORDER BY で未修飾
SELECT u.id
FROM users u
JOIN orders o ON o.user_id = u.id
ORDER BY id;               -- NG: ERROR: column reference "id" is ambiguous

-- 解決
SELECT u.id
FROM users u
JOIN orders o ON o.user_id = u.id
ORDER BY u.id;             -- OK
-- または SELECT リストの序数を使う（読みやすさ重視なら非推奨）
-- ORDER BY 1;

-- GROUP BY でも同様
SELECT id, count(*)
FROM users u JOIN orders o ON o.user_id = u.id
GROUP BY id;               -- NG

SELECT u.id, count(*)
FROM users u JOIN orders o ON o.user_id = u.id
GROUP BY u.id;             -- OK

CREATE TABLE payments (
  id bigint PRIMARY KEY,
  order_id bigint NOT NULL REFERENCES orders(id),
  amount numeric NOT NULL
);

-- 内側のクエリで id が複数テーブルにあり未修飾
SELECT u.id
FROM users u
WHERE EXISTS (
  SELECT 1
  FROM orders o
  JOIN payments p ON p.order_id = o.id
  WHERE id = u.id      -- NG: o.id と p.id のどちらか不明 → ERROR
);

-- 修正
SELECT u.id
FROM users u
WHERE EXISTS (
  SELECT 1
  FROM orders o
  JOIN payments p ON p.order_id = o.id
  WHERE o.id = u.id    -- OK: どの id かが明確
);

CREATE TABLE shipments (
  id bigint PRIMARY KEY,
  order_id bigint NOT NULL REFERENCES orders(id),
  shipped_at timestamp
);

-- USING は指定列（ここでは order_id）を1列に統合する一方で、
-- 他の同名列（id など）は統合されないため曖昧さが残る
SELECT id
FROM orders o
JOIN shipments s USING (order_id);
-- NG: o.id と s.id があり未修飾 → ERROR: column reference "id" is ambiguous

-- 修正：必要に応じて明示的に選ぶ
SELECT o.id AS order_id, s.id AS shipment_id
FROM orders o
JOIN shipments s USING (order_id);  -- OK

SELECT *
FROM users u JOIN orders o ON o.user_id = u.id
ORDER BY id;   -- NG: どの id か不明

-- 修正
SELECT u.*, o.*
FROM users u JOIN orders o ON o.user_id = u.id
ORDER BY o.id; -- OK（とはいえ * より列を列挙する方が推奨）

SELECT u.id, u.name, o.created_at
FROM users AS u
JOIN orders AS o ON o.user_id = u.id
WHERE u.name ILIKE '%suzuki%'
ORDER BY o.created_at DESC;

SELECT u.id AS user_id, o.id AS order_id, o.created_at AS ordered_at
FROM users u
JOIN orders o ON o.user_id = u.id
ORDER BY ordered_at DESC;  -- 別名で明確化

WITH o AS (
  SELECT id AS order_id, user_id, created_at FROM orders
), s AS (
  SELECT id AS shipment_id, order_id, shipped_at FROM shipments
)
SELECT u.id AS user_id, o.order_id, s.shipment_id, s.shipped_at
FROM users u
JOIN o ON o.user_id = u.id
LEFT JOIN s ON s.order_id = o.order_id
ORDER BY s.shipped_at DESC NULLS LAST;

CREATE OR REPLACE VIEW order_with_user AS
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 u.id = o.user_id;

-- 以降はビューから安全に参照
SELECT user_id, user_name, order_id
FROM order_with_user
ORDER BY order_id DESC;

-- 1) UPDATE ... FROM ... RETURNING の未修飾
UPDATE orders o
SET created_at = NOW()
FROM shipments s
WHERE s.order_id = o.id
RETURNING id;           -- NG: どの id か不明

UPDATE orders o
SET created_at = NOW()
FROM shipments s
WHERE s.order_id = o.id
RETURNING o.id;         -- OK

-- 2) DISTINCT ON と ORDER BY の未修飾
SELECT DISTINCT ON (u.id) u.id, o.created_at
FROM users u JOIN orders o ON o.user_id = u.id
ORDER BY u.id, created_at DESC;  -- NG: created_at が曖昧

SELECT DISTINCT ON (u.id) u.id, o.created_at
FROM users u JOIN orders o ON o.user_id = u.id
ORDER BY u.id, o.created_at DESC; -- OK

-- 3) 相関サブクエリの where で別名漏れ
SELECT u.id
FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE id = u.id   -- NG
);
-- 修正
SELECT u.id
FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.id = u.id -- OK
);

-- 4) 同名の created_at が多段 JOIN で衝突
SELECT created_at
FROM users u
JOIN orders o   ON o.user_id = u.id
JOIN payments p ON p.order_id = o.id;      -- NG

SELECT o.created_at
FROM users u
JOIN orders o   ON o.user_id = u.id
JOIN payments p ON p.order_id = o.id;      -- OK

-- ✅ 別名 + 修飾 + 別名活用 の基本形
SELECT
  u.id   AS user_id,
  u.name AS user_name,
  o.id   AS order_id,
  o.created_at AS ordered_at
FROM users     AS u
JOIN orders    AS o ON o.user_id = u.id
LEFT JOIN payments AS p ON p.order_id = o.id
WHERE u.name ILIKE '%keyword%'
GROUP BY u.id, u.name, o.id, o.created_at
HAVING COUNT(p.id) >= 0
ORDER BY ordered_at DESC
LIMIT 50 OFFSET 0;