Using DISTINCT for Clean Data: A PostgreSQL Beginner's Guide

Previously in this course, we explored grouping data with GROUP BY to perform analytical calculations. While GROUP BY is perfect for aggregating metrics, sometimes you simply need to see a unique list of values or identify distinct categories without performing math. That is where the DISTINCT clause comes in.

When working with real-world data, you often end up with result sets containing redundant information. DISTINCT allows you to cut through the noise and return only unique rows.

Eliminating Duplicates with DISTINCT

The DISTINCT keyword is a modifier you place immediately after SELECT. It tells PostgreSQL to look at the combination of all columns in your query and return only unique rows.

If you have a table of orders and you want to know which unique customer_ids have placed an order, a standard SELECT might return hundreds of rows with repeating IDs. Using DISTINCT collapses those into a single list.

Worked Example: Extracting Unique Categories

Imagine our store database has a products table, but we’ve accidentally populated it with multiple entries per category. To retrieve a clean list of all available categories, we use:

SQL
SELECT DISTINCT category 
FROM products;

If you select multiple columns, DISTINCT treats the entire row as a unique set. For example, SELECT DISTINCT category, supplier_id FROM products will return only rows where the combination of both columns is unique.

Advanced Filtering with DISTINCT ON

Standard DISTINCT is powerful, but sometimes it is too blunt. You might want to get one representative row per category—perhaps the most recently added product for each type.

For this, PostgreSQL provides a vendor-specific extension: DISTINCT ON.

Worked Example: Finding the Newest Product per Category

Suppose you want to see the latest product added to each category. Using DISTINCT ON (category) forces PostgreSQL to keep only the first row it encounters for each unique value in that column.

Crucially, you must include an ORDER BY clause to determine which row is "first."

SQL
SELECT DISTINCT ON (category) 
    product_name, 
    category, 
    created_at
FROM products
ORDER BY category, created_at DESC;

In this query:

1. DISTINCT ON (category) tells PostgreSQL to keep one row per unique category.
2. ORDER BY category, created_at DESC ensures that for every category, the row with the most recent created_at timestamp appears first, so it is the one kept.

Hands-on Exercise

Using our store's orders table:

1. Write a query to find all unique status values (e.g., 'pending', 'shipped', 'cancelled').
2. Write a query using DISTINCT ON to find the most recent order date for each unique customer_id.

Hint: Ensure you sort by the date in descending order to get the most recent record.

Common Pitfalls

• Forgetting ORDER BY with DISTINCT ON: If you use DISTINCT ON without a corresponding ORDER BY clause that starts with the same column, PostgreSQL cannot guarantee which row you will get. It will be effectively random.
• Performance Impact: DISTINCT requires PostgreSQL to sort or hash the entire result set to identify duplicates. On tables with millions of rows, this can be slow. If you find your queries lagging, consider analyzing query plans to see if an index can speed up the operation.
• Confusing it with GROUP BY: Use DISTINCT when you want a unique list of items. Use GROUP BY when you need to perform calculations (like SUM or COUNT) on those items.

FAQ

Q: Can I use DISTINCT on only one column if I select multiple columns? A: No. DISTINCT applies to the entire row combination. If you need a unique list of one column while keeping others, use DISTINCT ON.

Q: Is DISTINCT case-sensitive? A: Yes. 'Electronics' and 'electronics' are treated as two unique values.

Q: Does DISTINCT remove duplicates from the source table? A: Never. It only filters the result set returned to your client. Your stored data remains exactly as it was.

Recap

We have learned how to use DISTINCT to filter out redundant information and DISTINCT ON to retrieve specific, non-duplicate records based on your custom sorting criteria. These tools are essential for data cleanup and generating clean, readable reports from your store database.

Up next: We will dive into Common Table Expressions (CTEs) to break down complex, multi-step queries into readable, reusable segments.