PostgreSQL DISTINCT: Unique Values

You've just imported a massive CSV of customer feedback, and your first query returns 4,000 rows — but you know there are only a handful of distinct product names. Or you're staring at a SELECT that lists every order, but you need to know which customers have ordered at all. This is the exact pain DISTINCT solves: collapsing duplication so you can see the unique values hiding in your data. In this lesson, you'll learn how to use DISTINCT to find unique values, how it behaves in real queries, and the subtle edge cases that trip up even experienced developers.

The problem this lesson solves

SQL tables are built to hold rows — and rows repeat. A single customer_id can appear in dozens of order rows; a status column might repeat 'shipped' a hundred times in an events log. When you run a plain SELECT, you get every row back, duplicates included. That's fine for raw data, but it's useless when your question is "What are the possible values?" or "Who is in this dataset?"

Without DISTINCT, you end up scrolling through endless repeats, exporting to Excel to dedupe manually, or writing convoluted GROUP BY clauses just to get a list of unique items. DISTINCT is the dedicated, built-in solution. It's one keyword that transforms a messy result set into a clean list of unique values — perfect for reports, dropdown filters, data exploration, and sanity-checking your data quality.

This lesson is step 12 in the PostgreSQL Tutorial, part of the DevOps & backends track. You've already learned how to select columns, filter with WHERE, and order results. Now you'll learn to remove duplicates and see the true shape of your data — a critical skill for data analysis, debugging, and building user-facing features.

Core concept / mental model

Think of DISTINCT as a deduplication lens on your query results. The database engine runs the query, collects the result set (with all its duplicates), then applies the lens: any identical rows are collapsed into one. The output is a set of unique rows — no repeats.

Mental model: If you have a bag of marbles with colors repeated, SELECT DISTINCT color FROM marbles empties the bag and shows you one marble of each color.

Key definitions before we dive in:

• DISTINCT (single column): Returns unique values from one column.
• DISTINCT (multiple columns): Returns unique combinations across the listed columns. The combination (product, region) is considered a single unit; duplicates of that exact pair are removed.
• DISTINCT ON: A PostgreSQL-specific extension that returns the first row for each unique value of a specified column(s), along with any other columns you request.

Here's a quick way to visualize it:

Input rows (product):  ['laptop', 'laptop', 'desk', 'chair']
Output after DISTINCT: ['laptop', 'desk', 'chair']

This mental model will help you predict results before you even run a query.

How it works step by step

Now let's walk through the mechanics of using DISTINCT in an actual query.

1. Start with a normal SELECT to see all values. sql SELECT product FROM sales; This returns every row — duplicates included.

2. Add DISTINCT right after SELECT. sql SELECT DISTINCT product FROM sales; PostgreSQL scans the result set, removes rows that are exact duplicates, and returns one row per unique value.

3. Apply DISTINCT to multiple columns when you need unique combinations. sql SELECT DISTINCT product, region FROM sales; Here, each row is a unique product-region pair. If two rows have the same product and same region, only one survives.

4. Use DISTINCT ON for more control (PostgreSQL-specific). sql SELECT DISTINCT ON (product) product, region, sale_date FROM sales ORDER BY product, sale_date DESC; This returns one row per product — the first row in the sort order (here, the most recent sale_date).

5. Combine with COUNT to quantify uniqueness. sql SELECT COUNT(DISTINCT product) FROM sales; This counts unique products without listing them.

Why order matters: DISTINCT and ORDER BY interact in subtle ways. In standard SELECT DISTINCT, the ORDER BY columns must appear in the select list. With DISTINCT ON, the ORDER BY must start with the same columns as the DISTINCT ON expression, or you'll get an error.

Hands-on walkthrough

Let's get hands-on. First, spin up a quick sample table if you don't have one:

CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    product TEXT,
    region TEXT,
    sale_date DATE
);

INSERT INTO sales (product, region, sale_date) VALUES
    ('laptop', 'north', '2024-01-15'),
    ('laptop', 'south', '2024-01-16'),
    ('desk',   'north', '2024-01-15'),
    ('laptop', 'north', '2024-01-17'),
    ('chair',  'east',  '2024-01-18');

Now, run these queries and observe the output:

-- All products (with duplicates)
SELECT product FROM sales;

Output:

product
--------
laptop
laptop
desk
laptop
chair

-- Unique products
SELECT DISTINCT product FROM sales;

Output:

product
--------
laptop
chair
desk

Notice how 'laptop' appears only once. Now let's try unique combinations:

-- Unique product-region pairs
SELECT DISTINCT product, region FROM sales;

Output:

product | region
--------+-------
laptop  | north
laptop  | south
desk    | north
chair   | east

Because 'laptop' + 'north' appears twice in the table, it shows up once. Now let's see COUNT(DISTINCT ...):

-- How many unique products?
SELECT COUNT(DISTINCT product) AS unique_products FROM sales;

Output:

unique_products
----------------
3

Finally, a DISTINCT ON example — get the most recent sale for each product:

SELECT DISTINCT ON (product) product, region, sale_date
FROM sales
ORDER BY product, sale_date DESC;

Output:

product | region | sale_date
--------+--------+----------
laptop  | north  | 2024-01-17
desk    | north  | 2024-01-15
chair   | east   | 2024-01-18

Here, for 'laptop' we get the row with the latest sale_date.

Pro tip: Always test DISTINCT on a small subset first (add LIMIT 10 or a WHERE to filter) to sanity-check the results before running on a full table.

Compare options / when to choose what

DISTINCT isn't the only way to get unique values. Here's a comparison of the common alternatives:

When to choose what?

• Use plain DISTINCT when you want a list of unique values or combinations — the simplest, most readable choice.
• Use GROUP BY when you need to compute aggregates (like SUM or AVG) alongside your unique values.
• Use DISTINCT ON when you need the full row (other columns) for each unique value — something plain DISTINCT can't do.
• Use COUNT(DISTINCT col) when you only need a number, not the actual values.

Variations: Instead of DISTINCT, some developers use GROUP BY with MIN() or MAX() to get other columns, but that's often a workaround. DISTINCT ON is the cleaner PostgreSQL-native way. Another variation is using a UNION query to deduplicate (e.g., SELECT product FROM ... UNION SELECT product FROM ...), but that's less intuitive and slower.

Troubleshooting & edge cases

Even though DISTINCT is straightforward, there are several common pitfalls:

1. DISTINCT and ORDER BY mismatch.

-- This fails!
SELECT DISTINCT product FROM sales ORDER BY sale_date;

PostgreSQL will complain: for SELECT DISTINCT, ORDER BY expressions must appear in select list. Fix it by including the column in the select list, or use DISTINCT ON with the proper ordering.

2. NULL values are treated as a single group.

If a column has NULL in some rows, SELECT DISTINCT col returns one row with NULL. COUNT(DISTINCT col) does not count NULLs. This often surprises beginners.

3. Performance with DISTINCT on large tables.

DISTINCT performs a sort or hash operation, which can be expensive on millions of rows. If you're just checking for existence, consider using EXISTS or LIMIT 1 instead.

4. DISTINCT ON requires ORDER BY to start with the same columns.

Forgetting this leads to an error like SELECT DISTINCT ON expressions must match initial ORDER BY expressions. Always align them.

5. DISTINCT with text columns can ignore trailing spaces? Actually, in PostgreSQL, 'a' and 'a ' are different strings; DISTINCT treats them as distinct. This can cause surprising "duplicates" if your data has inconsistent whitespace — consider trimming first.

6. DISTINCT and ORDER BY on multiple columns.

You can order by columns not in the select list if they are functionally dependent, but it's safer to just include them.

Troubleshooting checklist:

• If you see duplicate rows still, check if you're missing a column in DISTINCT — with multiple columns, DISTINCT considers the combination.
• If COUNT(DISTINCT) returns a lower number than expected, check for NULL values.
• If you get an error about ORDER BY, review the column lists.
• If queries are slow, use EXPLAIN ANALYZE to see if the sort is the bottleneck.

What you learned & what's next

Great job! You've now mastered DISTINCT in PostgreSQL. Let's recap what you've accomplished:

• You can explain the core idea behind using DISTINCT to find unique values — it removes duplicate rows from your result set.
• You've completed a hands-on exercise that demonstrates DISTINCT, DISTINCT on multiple columns, COUNT(DISTINCT), and DISTINCT ON.
• You understand when to choose DISTINCT versus GROUP BY or DISTINCT ON based on your needs.

Key takeaways to remember:

• SELECT DISTINCT removes full-row duplicates; use it to get a list of unique values.
• SELECT DISTINCT col1, col2 removes duplicates based on the combination of those columns.
• COUNT(DISTINCT col) counts unique values, ignoring NULLs.
• DISTINCT ON returns the first row per unique value according to your ORDER BY — a powerful PostgreSQL-specific tool.
• Always align ORDER BY with DISTINCT columns to avoid errors.

What's next? In the next lesson, you'll build on this foundation by learning how to aggregate data with GROUP BY — a natural companion to DISTINCT where you'll compute sums, averages, and counts per category. With DISTINCT and GROUP BY in your toolkit, you'll be ready to extract meaningful insights from even the messiest datasets.

Keep practicing — try DISTINCT on your own tables and see how it simplifies your data exploration!