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.
Transform Your Career
Choose from our industry-leading programs designed for career success
Modern Software and AI Engineering Program
Master full-stack development with AI integration
+1000 moreModern Data Science and ML with specialisation in AI
Advanced data science techniques with AI specialization
+1000 moreAdvanced AIML with Specialisation in Agentic AI
Deep dive into AIML with focus on Agentic systems
+1000 moreDevOps, Cloud & AI Platform Engineering
Build and manage AI-powered cloud infrastructure
+1000 moreAI Engineering Advanced Certification by IIT-Roorkee
Premier AI engineering certification from IIT-Roorkee
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 |
Scaler Placement Report and Statistics
Scaler learners achieved 2.5x salary growth with average post-Scaler CTC reaching ₹23L.
Google
Amazon
Microsoft
Flipkart
Adobe1200+ morePostgreSQL 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 |
Turn Learning into Career Growth
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.
