SELECT DISTINCT

Introduction to PostgreSQL SELECT DISTINCT

By default, a SELECT statement can return multiple rows with identical values. SELECT DISTINCT eliminates those duplicates, retaining only one row for each unique value (or combination of values) in the specified columns.

The basic syntax for a single column:

SELECT DISTINCT column1
FROM table_name;

For multiple columns, DISTINCT evaluates uniqueness based on the combination of all listed columns:

SELECT DISTINCT column1, column2
FROM table_name;

To find distinct rows across all columns, use SELECT DISTINCT *:

SELECT DISTINCT *
FROM table_name;

SELECT DISTINCT examples

Consider a colors table with bcolor and fcolor columns containing these rows:

id | bcolor | fcolor
----+--------+--------
 1 | red    | red
 2 | red    | red
 3 | red    | null
 4 | null   | red
 5 | null   | null
 6 | green  | green
 7 | blue   | blue
 8 | blue   | blue

Distinct values on one column:

SELECT DISTINCT bcolor
FROM colors
ORDER BY bcolor;

Result:

bcolor
--------
blue
green
red
null
(4 rows)

The three red rows collapse to one, the two blue rows collapse to one, and both NULLs collapse to one NULL.

Distinct on multiple columns:

SELECT DISTINCT bcolor, fcolor
FROM colors
ORDER BY bcolor, fcolor;

Result:

bcolor | fcolor
--------+--------
blue   | blue
green  | green
red    | red
red    | null
null   | red
null   | null
(6 rows)

Here, (red, red) appears twice in the original table but is collapsed to one row. (red, null) only appears once, so it is kept as-is.

Practical use: auditing unique values

A common real-world use of SELECT DISTINCT is discovering what distinct values exist in a column before writing filter logic. For example, finding all distinct rental rates in a film catalog:

SELECT DISTINCT rental_rate
FROM film
ORDER BY rental_rate;

Result:

rental_rate
-------------
       0.99
       2.99
       4.99
(3 rows)

This confirms there are exactly three rental rate tiers, which you can then use in an IN clause or application logic.

How SELECT DISTINCT handles NULL

PostgreSQL treats NULL as a duplicate of another NULL for the purpose of DISTINCT. So if a column contains five NULL values, SELECT DISTINCT returns only one NULL. This is different from how = comparison works (where NULL = NULL is never TRUE).

SELECT DISTINCT vs. GROUP BY

• SELECT DISTINCT col and SELECT col FROM t GROUP BY col produce the same unique-value result, but GROUP BY lets you add aggregate functions like COUNT(*).
• For simply listing unique values without aggregation, SELECT DISTINCT is more concise and often clearer in intent.
• Performance is typically similar; for very large tables, check EXPLAIN ANALYZE to see whether a HashAggregate or Sort-based dedup is used.

Reference: PostgreSQL documentation — SELECT DISTINCT.