PostgreSQL SELECT DISTINCT Clause

SELECT DISTINCT will only return the unique rows of the result returned by the query. It will remove the duplicated rows from the result set. Postgres DISTINCT can also be used with multiple columns, then it removes duplicate rows on the basis of a combination of all the columns.

PostgreSQL SELECT DISTINCT Syntax

Syntax of Postgres DISTINCT for the single column is given below:

In the above syntax, unique rows will be selected on the basis of the value of columnName1.

Syntax of Postgres DISTINCT for multiple columns is given below:

In the above syntax, duplicate values will be removed based on the combination of values of all the specified columns(columnName1, columnName2, ...).

PostgreSQL SELECT DISTINCT Parameters

PostgreSQL SELECT DISTINCT have the following parameters:

columnName: It represents the column name(s) for which a unique value will be evaluated for the result set.
tableName: It represents the table name(s) from which the data is required to be fetched.
WHERE conditions(Optional): It is optional and is used to specify the conditions that are to be applied to the data to be fetched.

PostgreSQL SELECT DISTINCT examples

Let us assume we have a table named Colors, having columns id, textColor and backColor And the following data is inserted in the table.

id	textColor	backColor
1	blue	red
2	red	blue
3	red	green
4	blue	yellow
5	blue	yellow
6	red	yellow
7	green	red
8	green	blue
9	yellow	green
10	yellow	blue
11	red	blue
12	yellow	blue
13	yellow	green
14	red	green
15	red	yellow

Suppose we want to fetch the unique textColor from the table Colors, then we have to write the following query:

Query:

Output:

textColor
blue
red
green
yellow

PostgreSQL DISTINCT one column example

Below is the query to fetch a unique backColor from the table Colors.

Query:

Output:

backColor
red
blue
green
yellow

PostgreSQL DISTINCT multiple columns

If we specify multiple columns with the DISTINCT clause, then the unique value of the combination of all the columns will be selected.

Below is an example of using DISTINCT with columns textColor and backColor, so now it will only skip those rows which are duplicated in both textColor and backColor columns.

Query:

Output:

textColor	backColor
blue	red
red	blue
red	green
blue	yellow
red	yellow
green	red
green	blue
yellow	green
yellow	blue

PostgreSQL DISTINCT Clause: With DISTINCT ON Clause

Postgres DISTINCT On clause includes the unique values, removes all duplicate values of the first column and keeps only a single row for every unique of the first column.

DISTINCT ON Clause Syntax:

In the above syntax, the result set will include distinct values of columnName1, and for each distinct value, it will include the first occurrence row of that value based on the specified ORDER BY clause.

Query:

Output:

textcolor	id
blue	1
green	7
red	2
yellow	9

Explanation: The above query will include unique values of textColor and id will be chosen corresponds to the first occurrence of every textColor value.

PostgreSQL DISTINCT Clause: Multiple Tables

Let us create two tables Employee and Department, with the following attributes.

Now let us insert some data in both the tables:

Now we will use the DISTINCT clause to get the unique values and combine both tables using join.

Query:

Output:

empid	name	depart_id	depart_name
1	John	D1	Training
2	Hanna	D2	Hiring
3	Steve	D3	Production

Conclusion

SELECT DISTINCT will only return the unique rows of the result returned by the query.
Postgres DISTINCT can also be used with multiple columns.
Postgres DISTINCT On clause includes the unique values, removes all duplicate values of the first column and keeps only a single row for every unique of the first column.