PostgreSQL DISTINCT statement

PostgreSQL DISTINCT statement is used to remove duplicates from the result set. DISTINCT can be used only with SELECT operators.

Syntax for DISTINCT statement in PostgreSQL

SELECT DISTINCT | DISTINCT ON (distinct_expressions_id)
expressions_id
FROM tabs
[WHERE conds];

Parameters and statement arguments

• Expressions_expressions_id – Expressions used to delete duplicates.
• Column – The column or calculation you want to get.
• tabs – The tables from which you want to get the records. The FROM operator must specify at least one table.
• WHERE conds – Optional. The conditions to be met for the records to be selected.

Note:

• If only one expression is specified in DISTINCT, the query will return unique values for that expression.
• If several expressions are specified in DISTINCT, the query retrieves the unique combinations for the listed expressions.
• If DISTINCT ON keywords are specified, the query will return unique values for Different_expressions and other fields for the selected entries based on ORDER BY (limit 1).
• In PostgreSQL, DISTINCT does not ignore NULL values. Therefore when using DISTINCT in your SQL statement, your resulting set will contain NULL as a separate value.

Example of a DISTINCT statement with one expression

Let’s look at the simplest example of DISTINCT in PostgreSQL. We can use the DISTINCT operator to return a single field that removes duplicates from the result set.
For example:

SELECT DISTINCT last_name
FROM contacts
ORDER BY last_name;

In this PostgreSQL example, DISTINCT will return all unique last_name values from the contacts table.

An example of a DISTINCT statement with multiple expressions

Let’s see how you can use the PostgreSQL DISTINCT statement to remove duplicates from more than one field in your SELECT statement.
For example:

SELECT DISTINCT last_name, city, state
FROM contacts
ORDER BY last_name, city, state;

This example will return each unique combination of last_name, city, and state from the contacts table. In this case, DISTINCT applies to each field specified after the DISTINCT keyword and therefore returns different combinations.

Example of a DISTINCT ON statement

One thing that is unique in PostgreSQL, compared to other databases, is that you have another option when using the DISTINCT operator, which is called DISTINCT ON.

DISTINCT ON will return only the first line for DISTINCT ON (diver_expressions) based on the ORDER BY operator provided in the query. Any other fields listed in the SELECT statement will be returned for this first line. This is similar to running LIMIT in 1 for each combination of DISTINCT ON (Different_expressions).

Let’s have a closer look at how to use DISTINCT ON in a DISTINCT operator and what it returns.
In this way, we could change the example above :

SELECT DISTINCT ON (last_name) last_name, city, state
FROM contacts
ORDER BY last_name, city, state;

This DISTINCT example, which uses DISTINCT ON keywords, will return all unique values of last_name. But in this case, for each unique value of last_name, it will return only the first unique last_name record it encounters, based on the ORDER BY operator together with the city and state values from that record.

It does not return unique combinations of last_name, city, and state. In fact, it performs a LIMIT of 1 for each DISTINCT ON (last_name) and returns the corresponding city and state values after it has selected the returned records.

PostgreSQL: Distinct | Course