Menu

PostgreSQL DISTINCT Usages

Use PostgreSQL SELECT DISTINCT to remove duplicate rows, or DISTINCT ON with ORDER BY to keep a chosen row from each group.

Updated on

PostgreSQL SELECT DISTINCT removes duplicate rows from the result. PostgreSQL also supports DISTINCT ON, which keeps the first row in each group; use ORDER BY to control which row comes first.

PostgreSQL DISTINCT syntax

To return a result set with no duplicate rows, use the SELECT statement with the DISTINCT keyword:

Here is the syntax for DISTINCT:

SELECT
   DISTINCT column1 [, column2, ...]
FROM
   table_name;

Explanation:

  • The keyword DISTINCT should be specified after SELECT.
  • Specify the columns to evaluate for duplicates after the DISTINCT keyword .
  • Separate multiple column names with commas. PostgreSQL removes rows that have the same values in every selected expression; it does not concatenate the values.
  • DISTINCT * compares whole output rows, including every selected column.

PostgreSQL also provides DISTINCT ON (expression) to keep the first row of each set of duplicates using the following syntax:

SELECT
   DISTINCT ON (column1) column_alias,
   column2
FROM
   table_name
ORDER BY
   column1,
   column2;

Use the ORDER BY clause with DISTINCT ON (expression) to control which row is kept from each group. Without a suitable ordering, the chosen row is unpredictable.

The DISTINCT ON expressions must match the leftmost ORDER BY expressions. Additional sort expressions can specify which row has precedence within each group.

PostgreSQL DISTINCT Examples

We will use the tables in the Sakila sample database for demonstration, please install the Sakila sample database in PostgreSQL first.

Unless a query specifies ORDER BY, PostgreSQL does not guarantee the order of its result rows; the output tables below show one possible order.

To retrieve all ratings of films from the film table, use the following statement:

SELECT
    DISTINCT rating
FROM
    film;

 rating
--------
 R
 PG-13
 G
 PG
 NC-17
(5 rows)

Here, in order to find all the films ratings, we use DISTINCT rating, so that each film rating appears only once in the result set.

To retrieve all rent amounts from the film table, use the following statement:

SELECT
    DISTINCT rental_rate
FROM
    film;

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

Here, in order to find all the film rental amounts, we use DISTINCT rental_rate, so that each film rental amount appears only once in the result set.

To retrieve all combinations of ratings and rental amounts from the film table, use the following statement:

SELECT
    DISTINCT rating, rental_rate
FROM
    film
ORDER BY rating;

 rating | rental_rate
--------+-------------
 G      |        0.99
 G      |        4.99
 G      |        2.99
 PG     |        2.99
 PG     |        0.99
 PG     |        4.99
 PG-13  |        4.99
 PG-13  |        0.99
 PG-13  |        2.99
 R      |        0.99
 R      |        2.99
 R      |        4.99
 NC-17  |        0.99
 NC-17  |        2.99
 NC-17  |        4.99
(15 rows)

Here, DISTINCT rating, rental_rate returns each combination once. The query orders by rating, but the order of rental_rate values within each rating is unspecified because it is not included in ORDER BY.

If you want to return the first row for each set of films ratings, use the DISTINCT ON:

SELECT
    DISTINCT ON (rating) rating,
    film_id,
    title
FROM
    film
ORDER BY rating, film_id DESC;

 rating | film_id |      title
--------+---------+------------------
 G      |       2 | ACE GOLDFINGER
 PG     |       1 | ACADEMY DINOSAUR
 PG-13  |       7 | AIRPLANE SIERRA
 R      |       8 | AIRPORT POLLOCK
 NC-17  |       3 | ADAPTATION HOLES

DISTINCT and NULL

For duplicate elimination, PostgreSQL treats NULL values as equal, so duplicate rows that contain NULL in the same output positions collapse to one row.

For example the following SQL statement returns multiple rows of NULL records:

SELECT NULL nullable_col
UNION ALL
SELECT NULL nullable_col
UNION ALL
SELECT NULL nullable_col;

 nullable_col
--------------
 <null>
 <null>
 <null>
(3 rows)

Here, we have 3 rows, each of which has a nullable_col column value of NULL.

After using DISTINCT:

SELECT
    DISTINCT nullable_col
FROM
    (
    SELECT NULL nullable_col
    UNION ALL
    SELECT NULL nullable_col
    UNION ALL
    SELECT NULL nullable_col
    ) t;

 nullable_col
--------------
 <null>
(1 row)

This example uses to UNION ALL simulate a recordset containing multiple NULL values.

Conclusion

Use DISTINCT to remove duplicate output rows and DISTINCT ON to select one ordered row from each group:

  • The SELECT DISTINCT statement returns a result set with no duplicate rows.
  • You can specify one or more expressions after DISTINCT, or use DISTINCT * for whole rows.
  • DISTINCT collapses duplicate rows, including rows with matching NULL positions.
  • DISTINCT ON keeps one row per group, and ORDER BY determines which row comes first.

For the complete syntax and processing rules, see PostgreSQL’s SELECT reference.

Advertisement