Khắc phục lỗi PostgreSQL: 'more than one row returned by a subquery used as an expression'

Tại sao lỗi này xảy ra

Bạn chạy một truy vấn vốn hoạt động hoàn hảo vào hôm qua, nhưng hôm nay nó lại gặp lỗi. PostgreSQL đang báo cho bạn biết rằng nó mong đợi một giá trị đơn lẻ (scalar), nhưng truy vấn con của bạn lại trả về một danh sách gồm hai hàng trở lên. Điều này thường xảy ra khi mối quan hệ 1:1 trong dữ liệu của bạn đột ngột trở thành mối quan hệ 1:N (một-nhiều).

Xác định nguyên nhân gốc rễ

Trong SQL, một "scalar subquery" (truy vấn con vô hướng) phải trả về chính xác một cột và một hàng. Nếu nó trả về không hàng nào, PostgreSQL sẽ coi kết quả là NULL. Tuy nhiên, nếu nó trả về từ hai hàng trở lên, engine sẽ không biết nên sử dụng giá trị nào và dừng thực thi. Bạn thường sẽ thấy lỗi này ở ba vị trí:

• Trong danh sách SELECT để lấy một giá trị liên quan.
• Ở phía bên phải của một toán tử so sánh như =, <, hoặc >.
• Trong mệnh đề UPDATE ... SET khi gán một giá trị mới cho một cột.

Các bước khắc phục

1. Xác định chính xác dữ liệu gây lỗi

Trước khi thay đổi mã nguồn, hãy tìm các hàng gây ra xung đột. Ví dụ, nếu bạn đang lấy email dựa trên profile ID, hãy kiểm tra các bản ghi trùng lặp. Sử dụng truy vấn này để tìm các ID liên kết với nhiều bản ghi:

SELECT profile_id, COUNT(*)
FROM users
GROUP BY profile_id
HAVING COUNT(*) > 1;

Nếu kết quả trả về có dữ liệu, tính toàn vẹn dữ liệu của bạn đã bị vi phạm. Có thể bạn có hai người dùng chia sẻ cùng một profile_id, điều này phá vỡ logic của bạn.

2. Sử dụng LIMIT 1 để khắc phục nhanh

Nếu bạn chỉ quan tâm đến bản ghi gần đây nhất, hãy thêm LIMIT 1. Đây là một giải pháp tạm thời phổ biến cho các báo cáo mà tính chính xác tuyệt đối ít quan trọng hơn việc truy vấn chạy thành công. Luôn kết hợp điều này với ORDER BY để đảm bảo kết quả nhất quán.

SELECT 
    id, 
    (SELECT email FROM users 
     WHERE profile_id = profiles.id 
     ORDER BY created_at DESC LIMIT 1) as user_email
FROM profiles;

3. Thay đổi '=' thành 'IN' hoặc 'ANY'

Khi truy vấn con nằm trong mệnh đề WHERE, cách khắc phục thường đơn giản là thay đổi toán tử. Sử dụng = buộc PostgreSQL phải tìm một giá trị duy nhất. Chuyển sang IN sẽ báo cho cơ sở dữ liệu rằng bạn chấp nhận khớp với một danh sách các giá trị.

Truy vấn gây lỗi:

SELECT * FROM orders 
WHERE user_id = (SELECT id FROM users WHERE status = 'đang hoạt động');

Truy vấn đã sửa:

SELECT * FROM orders 
WHERE user_id IN (SELECT id FROM users WHERE status = 'đang hoạt động');

4. Tổng hợp kết quả (Aggregate)

Đôi khi bạn cần tất cả dữ liệu, chỉ là không phải dưới dạng nhiều hàng. Nếu một người dùng có ba địa chỉ email, bạn có thể sử dụng string_agg để kết hợp chúng thành một chuỗi duy nhất, ngăn cách bởi dấu phẩy. Cách này thỏa mãn yêu cầu "một hàng" trong khi vẫn bảo toàn dữ liệu của bạn.

SELECT 
    id, 
    (SELECT string_agg(email, ', ') FROM users WHERE profile_id = profiles.id) as all_emails
FROM profiles;

5. Chuyển đổi sang JOIN (Tốt nhất cho hiệu năng)

Các truy vấn con trong danh sách SELECT thường chậm vì chúng phải thực thi một lần cho mỗi hàng trong tập kết quả. Nếu bạn có 100.000 profile, PostgreSQL có thể phải thực thi 100.000 truy vấn con. Một phép LEFT JOIN hiệu quả hơn đáng kể và xử lý được nhiều kết quả khớp bằng cách tạo thêm các hàng thay vì gây lỗi.

SELECT 
    p.id, 
    u.email
FROM profiles p
LEFT JOIN users u ON u.profile_id = p.id;

Các bước xác minh

Sau khi áp dụng bản sửa lỗi, hãy xác minh rằng logic truy vấn con của bạn không còn tạo ra các bản ghi trùng lặp. Chạy lại truy vấn kiểm tra của bạn:

SELECT profile_id FROM users GROUP BY profile_id HAVING COUNT(*) > 1;

Nếu kết quả trả về không có hàng nào, mối quan hệ 1:1 của bạn đã được khôi phục. Nếu bạn đã sử dụng phương pháp JOIN hoặc LIMIT, hãy chạy truy vấn chính và đảm bảo số lượng hàng khớp với mong đợi. Một phép JOIN có thể làm tăng tổng số hàng của tập kết quả nếu dữ liệu trùng lặp vẫn còn tồn tại.

Mẹo chuyên sâu

• Ưu tiên EXISTS: Nếu bạn chỉ cần kiểm tra xem một bản ghi có tồn tại hay không, hãy sử dụng WHERE EXISTS (SELECT 1 FROM ...). Nó nhanh hơn một truy vấn con vô hướng và không bao giờ ném lỗi "more than one row".
• Lateral Joins: Đối với logic phức tạp khi bạn cần bản ghi liên quan "đầu tiên" nhưng muốn hiệu năng tốt hơn truy vấn con, hãy sử dụng LEFT JOIN LATERAL. Nó cho phép bạn chạy một truy vấn con cho mỗi hàng trong khi vẫn truy cập được các cột từ bảng cha.
• Ràng buộc cơ sở dữ liệu: Phòng bệnh hơn chữa bệnh. Nếu một cột chỉ được phép có một kết quả khớp duy nhất, hãy thêm ràng buộc UNIQUE. Điều này buộc lỗi phải xảy ra trong quá trình nhập dữ liệu thay vì trong một thao tác đọc quan trọng.