Sửa lỗi PostgreSQL ERROR: division by zero trong SQL Queries

Chuyện gì đang xảy ra

Bạn chạy một truy vấn có phép chia và PostgreSQL báo lỗi:

ERROR:  division by zero

Mẫu số được tính ra bằng không tại runtime. PostgreSQL dừng ngay lập tức — không có kết quả một phần, không có cảnh báo, chỉ là một lỗi cứng.

Các trường hợp thường gặp:

Cột phần trăm hoặc tỷ lệ (sales / total_sales * 100)
Tính trung bình thủ công (SUM(x) / COUNT(y)) khi một nhóm rỗng
Truy vấn tổng hợp mà một số nhóm có không bản ghi nào
Truy vấn KPI như doanh thu-trên-người-dùng trên dữ liệu thưa thớt

Tái hiện lỗi

Trường hợp đơn giản nhất:

SELECT 10 / 0;
-- ERROR:  division by zero

Trong một bảng thực tế:

SELECT
  department,
  total_revenue / headcount AS revenue_per_person
FROM department_stats;
-- Ổn cho đến khi một phòng ban có headcount = 0

Truy vấn chỉ bị lỗi khi thực sự gặp một hàng có headcount = 0. Dữ liệu test hiếm khi có giá trị không — đó chính xác là lý do lỗi này xuất hiện ở production trước, không phải ở môi trường dev.

Debug: Tìm các hàng gây lỗi

Trước khi chỉnh sửa truy vấn, hãy xác định rõ vấn đề:

-- Những hàng nào có mẫu số bằng không?
SELECT department, headcount
FROM department_stats
WHERE headcount = 0;

-- Với tổng hợp: tìm các nhóm rỗng trong bảng nguồn
SELECT department, COUNT(*) AS row_count
FROM sales
GROUP BY department;

Chỉ có 2 hàng hay đến 2.000 hàng? Con số đó quyết định cách sửa nào hợp lý hơn.

Cách sửa 1: NULLIF — Nhanh nhất

NULLIF(a, b) trả về NULL khi a = b, ngược lại trả về a. Bọc mẫu số của bạn lại:

SELECT
  department,
  total_revenue / NULLIF(headcount, 0) AS revenue_per_person
FROM department_stats;

Khi headcount bằng 0, phép chia trở thành total_revenue / NULL, cho ra NULL — không phải lỗi. Hàng đó vẫn xuất hiện trong kết quả, chỉ là cột đó có giá trị NULL.

Muốn trả về 0 thay vì NULL? Thêm COALESCE:

SELECT
  department,
  COALESCE(total_revenue / NULLIF(headcount, 0), 0) AS revenue_per_person
FROM department_stats;

Cách sửa 2: CASE WHEN — Kiểm soát rõ ràng hơn

Khi giá trị dự phòng không đơn giản, CASE WHEN cho bạn toàn quyền kiểm soát:

SELECT
  department,
  CASE
    WHEN headcount = 0 THEN NULL
    ELSE total_revenue / headcount
  END AS revenue_per_person
FROM department_stats;

Hoặc trả về một giá trị đặc biệt mà ứng dụng có thể nhận biết:

SELECT
  department,
  CASE
    WHEN headcount = 0 THEN -1  -- báo hiệu "không có dữ liệu" cho phía gọi
    ELSE total_revenue / headcount
  END AS revenue_per_person
FROM department_stats;

CASE WHEN xử lý short-circuit — PostgreSQL kiểm tra điều kiện trước và không bao giờ thực hiện phép chia nếu điều kiện bảo vệ khớp. Dễ đọc hơn so với lồng nhau NULLIF/COALESCE khi có logic nghiệp vụ phức tạp.

Cách sửa 3: Truy vấn tổng hợp

Chia cho không trong tổng hợp thường xuất phát từ các nhóm rỗng:

-- COUNT = 0 cho một danh mục không có hàng nào → lỗi
SELECT
  category,
  SUM(amount) / COUNT(order_id) AS avg_order_value
FROM orders
GROUP BY category;

Bọc COUNT bằng NULLIF:

SELECT
  category,
  SUM(amount) / NULLIF(COUNT(order_id), 0) AS avg_order_value
FROM orders
GROUP BY category;

Thậm chí tốt hơn — với tính trung bình, hãy dùng AVG(). Nó tự xử lý tập rỗng bằng cách trả về NULL, không cần bọc thêm gì:

-- AVG() không bao giờ báo lỗi chia cho không
SELECT category, AVG(amount) AS avg_order_value
FROM orders
GROUP BY category;

Cách sửa 4: Tính phần trăm

Truy vấn phần trăm là nơi lỗi này xuất hiện thường xuyên nhất. Một mẫu điển hình:

-- Rủi ro: chia số nguyên + tổng có thể bằng không
SELECT
  status,
  COUNT(*) * 100 / total AS pct
FROM orders
CROSS JOIN (SELECT COUNT(*) AS total FROM orders) t
GROUP BY status, total;

Phiên bản an toàn:

SELECT
  status,
  ROUND(
    COUNT(*) * 100.0 / NULLIF(total, 0),
    2
  ) AS pct
FROM orders
CROSS JOIN (SELECT COUNT(*) AS total FROM orders) t
GROUP BY status, total;

Hai cách sửa trong một: NULLIF bảo vệ khỏi giá trị không, và 100.0 (số thực) thay vì 100 (số nguyên) tránh bị cắt cụt khi chia. Một truy vấn trả về 33 thay vì 33.33 cũng là một loại lỗi theo cách riêng của nó.

Cách sửa 5: Ràng buộc ở cấp bảng

Nếu một cột như headcount không bao giờ được bằng không, hãy khai báo điều đó trong schema:

ALTER TABLE department_stats
  ADD CONSTRAINT headcount_positive CHECK (headcount > 0);

Dữ liệu xấu không bao giờ được nhập vào, nên truy vấn không bao giờ gặp trường hợp bằng không. Kết hợp với NOT NULL:

ALTER TABLE department_stats
  ALTER COLUMN headcount SET NOT NULL,
  ADD CONSTRAINT headcount_positive CHECK (headcount > 0);

Đây là cách sửa lâu dài nhất — nhưng chỉ áp dụng khi giá trị không thực sự là dữ liệu không hợp lệ, không phải trạng thái rỗng hợp lệ.

Kiểm tra lại sau khi sửa

Kiểm tra nhanh với giá trị thực:

-- Kết quả phải là NULL, không phải lỗi
SELECT 100 / NULLIF(0, 0);
-- Kết quả: NULL

-- Với fallback COALESCE
SELECT COALESCE(100 / NULLIF(0, 0), 0);
-- Kết quả: 0

Kiểm tra với bảng thực của bạn bằng cách dùng CTE để chèn một hàng có giá trị không:

WITH test_data AS (
  SELECT 'Engineering' AS dept, 0 AS headcount, 50000 AS revenue
)
SELECT
  dept,
  revenue / NULLIF(headcount, 0) AS rev_per_person
FROM test_data;
-- Kết quả mong đợi: Engineering | NULL  (không có lỗi)

Sau đó chạy lại truy vấn gốc bị lỗi. Nó sẽ hoàn thành mà không có vấn đề gì.

Tóm tắt nhanh

Cách sửa đơn giản nhất: / NULLIF(denominator, 0)
Trả về 0 thay vì NULL: COALESCE(x / NULLIF(y, 0), 0)
Logic phức tạp: CASE WHEN y = 0 THEN ... ELSE x / y END
Tính trung bình: dùng AVG() — tự xử lý tập rỗng
Ngăn chặn từ gốc: thêm ràng buộc CHECK (column > 0)

Bài học rút ra

Chín trong mười trường hợp, nguyên nhân gốc rễ là chất lượng dữ liệu. Một cột được thiết kế để chứa giá trị dương đã âm thầm chấp nhận giá trị không vì không có ràng buộc nào ngăn chặn. NULLIF chữa triệu chứng trong truy vấn — nhưng đáng để truy ngược lại xem những giá trị không đó là trạng thái rỗng hợp lệ hay là lỗi trong pipeline dữ liệu.

Với báo cáo và dashboard nơi dữ liệu thưa thớt là bình thường, NULLIF là lựa chọn mặc định phù hợp. Với truy vấn giao dịch nơi mẫu số bằng không báo hiệu dữ liệu bị hỏng, hãy áp dụng ràng buộc CHECK ở cấp bảng và sửa nguồn dữ liệu đầu vào.