Debugging queries

In plain English

When a query returns the wrong results, the first step is to narrow it down by adding a WHERE clause so you only look at the rows you care about. Use EXPLAIN to see how the database plans to run your query, which helps you spot slow full-table scans. To find duplicate rows, group by the columns that should be unique and keep only the groups that appear more than once. Remember that NULL is not equal to anything, so checking for missing values means using IS NULL rather than = NULL. While exploring, add a LIMIT so you only fetch a small sample until you are sure the query is right.

Step by step

1. 1Add a WHERE clause to shrink the result down to the rows you care about.
2. 2Run EXPLAIN to see whether the database is scanning a whole table.
3. 3Use GROUP BY with HAVING COUNT(*) > 1 to surface duplicate rows.
4. 4Test for missing values with IS NULL and IS NOT NULL.
5. 5Add LIMIT while exploring, then remove it for the final query.

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  customer TEXT NOT NULL,
  total NUMERIC(10, 2) NOT NULL,
  status TEXT
);

SELECT id, customer, total
FROM orders
WHERE total > 100
LIMIT 20;

EXPLAIN SELECT * FROM orders WHERE customer = 'Ada';

SELECT customer, COUNT(*)
FROM orders
GROUP BY customer
HAVING COUNT(*) > 1;

SELECT id, customer, status
FROM orders
WHERE status IS NULL;

Common mistakes

• Comparing a value to NULL with = instead of IS NULL, which returns no rows.
• Scanning a huge result set while exploring instead of limiting it first.
• Forgetting to add a WHERE clause and editing far more rows than intended.
• Searching for duplicate rows without knowing which columns should be unique.