Postgres by Example: DISTINCT

DISTINCT removes duplicate rows from the result set. The deduplication considers the entire row of the SELECT — DISTINCT a, b is "distinct combinations of (a, b)," not "distinct a, then any b." PostgreSQL also offers a more powerful sibling, DISTINCT ON (expr), which keeps one row per distinct value of expr — useful for "the most recent X per Y" queries.

What you'll learn:

• Eliminating duplicates with DISTINCT
• How DISTINCT applies to all columns in the SELECT list
• The PostgreSQL-specific DISTINCT ON (expr)
• How NULL is treated by DISTINCT
• When to use GROUP BY instead

-- Unique values in a column
SELECT DISTINCT name FROM fruits ORDER BY name LIMIT 5;

-- Distinct combinations of multiple columns
SELECT DISTINCT id, name FROM fruits ORDER BY id LIMIT 3;

-- DISTINCT ON: keep one row per distinct expression value.
-- Here: one row per first letter of the name, picking the smallest id.
SELECT DISTINCT ON (substring(name, 1, 1)) substring(name, 1, 1) AS letter, id, name
FROM fruits
ORDER BY substring(name, 1, 1), id;

-- DISTINCT treats NULL as a single value
SELECT DISTINCT n FROM (VALUES (1), (NULL), (NULL), (2)) AS t(n) ORDER BY n;

In our fruits table every name is already unique, so DISTINCT name returns the same rows as SELECT name. With multiple columns, DISTINCT requires all listed values to be the same for two rows to count as duplicates.

DISTINCT ON (substring(name, 1, 1)) is PostgreSQL's superpower for "first row per group" queries. The ON (...) expressions define the groups; the ORDER BY decides which row from each group is kept (the first one after sorting). The trick is that the ON expressions must appear at the start of the ORDER BY — otherwise PostgreSQL cannot guarantee which row you mean by "the first."

For NULL handling, DISTINCT considers two NULLs equal — counterintuitive given that NULL = NULL is unknown, but it is what the standard requires.

To run:

$ psql -f source/distinct.sql postgres
    name
------------
 apple
 banana
 cherry
 date
 elderberry
(5 rows)
 ...

Common pitfalls:

• SELECT DISTINCT a, b FROM t is not the same as SELECT DISTINCT a, (b) FROM t or "distinct values of a, pick any b." It deduplicates (a, b) pairs.
• DISTINCT and ORDER BY interact: any column in ORDER BY not in the DISTINCT list will cause an error (you cannot order by something that may have multiple values per distinct group).
• DISTINCT ON is non-standard SQL. Code that needs to be portable should use a window function (ROW_NUMBER() OVER (PARTITION BY ...)) instead — covered in a later lesson.
• SELECT DISTINCT on a large table forces a sort or hash; if you only need to know "are there duplicates?" use GROUP BY ... HAVING COUNT(*) > 1 or an aggregate.

Tip: count(DISTINCT col) counts distinct non-null values of col. It is slower than count(*) because it must deduplicate, but it answers "how many different X are there?" without needing a subquery.

Try it: Insert another row with an existing name (e.g. INSERT INTO fruits (id, name) VALUES (10, 'apple');) and run SELECT DISTINCT name FROM fruits ORDER BY name; — the count should not change. Then use DISTINCT ON (name) to keep the largest id per name (hint: ORDER BY name, id DESC).

Source: distinct.sql

Next: NULLs

Home: Postgres by Example