EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login

PostgreSQL DISTINCT

By Sohel SayyadSohel Sayyad

Home » Data Science » Data Science Tutorials » PostgreSQL Tutorial » PostgreSQL DISTINCT

postresql distnict

Overview of PostgreSQL DISTINCT

We can remove the duplicate rows from a statement’s result set by using a PostgreSQL DISTINCT clause in the SELECT statement. For all groups of duplicate rows, the PostgreSQL DISTINCT clause keeps only one row. PostgreSQL DISTINCT  removes all duplicate rows and maintains only one row for a group of duplicates rows. We can use the PostgreSQL DISTINCT clause on multiple columns of a table.

How to Use DISTINCT in PostgreSQL?

Let’s understand the DISTINCT clause syntax as follows:

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Syntax #1

SELECT
DISTINCT column_name1
FROM
table_name;

Explanation: To evaluate the duplicate rows, we use the values from the column_name1 column.

Syntax #2

The PostgreSQL DISTINCT clause evaluates the combination of all defined columns’ different values to evaluate the duplicates rows if we have specified the DISTINCT clause with multiple column names.

SELECT
DISTINCT column_name1, column_name2
FROM
table_name;

Explanation: In the above statement, to evaluate the duplicate rows, the values of column_name1 and column_name2 values are getting used combined.

Syntax #3

We can use the PostgreSQL DISTINCT ON clause or expression to maintain  the “first” row for a group of duplicates from the result set using the following syntax:

SELECT
DISTINCT ON (column_name1) column_name_alias,
column_name2
FROM
table_name
ORDER BY
column_name1,
column_name2;

Explanation: The SELECT statement returns the randomly ordered rows; hence the firstmost row of every resulting group is also random. To make the resulting set obvious, we can use the DISTINCT ON clause along with the ORDER BY clause.

Examples of PostgreSQL DISTINCT

We will create a table of name ColorProperties. Also, add some data into the ColorProperties table for understanding the DISTINCT clause.

Use the following statement to create the ColorProperties table, which consists of three columns:

  • color_id,
  • back_color and
  • fore_color.

Code:

CREATE TABLE ColorProperties (
color_id serial NOT NULL PRIMARY KEY,
back_color VARCHAR,
fore_color VARCHAR
);

  • We will insert some rows into the ColorProperties table with the help of the INSERT statement as follows:

Code:

INSERT INTO ColorProperties (back_color, fore_color)
VALUES
('blue', 'red'),
('green', 'blue'),
('red', 'red'),
('blue', 'green'),
('green', 'red'),
('blue', 'blue'),
('green', 'green'),
('red', 'red'),
('red', NULL),
(NULL, 'red'),
('red', 'green'),
('red', 'blue');

Popular Course in this category
Sale
PostgreSQL Course (2 Courses, 1 Project)2 Online Courses | 1 Hands-on Project | 7+ Hours | Verifiable Certificate of Completion | Lifetime Access
4.5 (6,548 ratings)
Course Price

View Course

Related Courses
  • Now retrieve the data from the ColorProperties table with the help of the SELECT statement as follows:

Code:

SELECT
color_id,
back_color,
fore_color
FROM
ColorProperties;<c/doe>

Output: After executing the above statement, we will following the result.

PostgreSQL DISTINCT - 1

1. Single One Column

To fetch the unique values of the column named fore_color, we will use the following statement. Also, to sort output results in alphabetical order, we will use the ORDER BY clause for the values in the fore_color column.

Code:

SELECT
DISTINCT fore_color
FROM
ColorProperties
ORDER BY
fore_color;

Output:

 Single One Column

2. Multiple Columns

Let’s understand how to use the DISTINCT clause on multiple columns using the following statement:

Code:

SELECT
DISTINCT fore_color,
back_color
FROM
ColorProperties
ORDER BY
fore_color,
Back_color;

Output:

Multiple Columns

  • As the SELECT DISTINCT clause contains back_color and fore_color so for removing the duplicate rows of the result, set the PostgreSQL combines values of both columns back_color and fore_color etc.
  • The PostgreSQL statement will give us the unique values for both columns back_color and fore_color  from the ColorProperties table. RED COLOR is present in both columns back_color and  fore_color in the ColorProperties table. As we added back_color and fore_color with the DISTINCT clause, only one-row entry kept for duplicate rows.

3. PostgreSQL with DISTINCT ON

To get the sorted result set, we should define the statement as follows: remove all duplicate rows and keep a one-row entry for all.

Code:

SELECT
DISTINCT ON
(back_color) backgroundcolor,
fore_color
FROM
ColorProperties
ORDER BY
back_color,
fore_color;

Output:

PostgreSQL DISTINCT - 4

4. Multiple Tables

Let’s consider we have two tables named: students and departments.

CREATE TABLE students (
name text not null,
department_id text not null,
CONSTRAINT students_pk PRIMARY KEY (name)
);
INSERT INTO students (name, department_id) VALUES
('Jacob', '101'),
('David', '102'),
('John', '103');
CREATE TABLE departments (
department_id text not null,
department_name text not null
);
INSERT INTO departments (department_id, department_name) VALUES
('101', 'Computer'),
('102', 'Electrical'),
('103', 'Mechanical');

Let’s have a look at the following statement, which joins student and department tables.

Code:

SELECT DISTINCT ON (s.department_id) s.department_id, s.name, d.department_name
FROM students s
JOIN departments d ON d.department_id = s.department_id
ORDER BY s.department_id DESC

  • We have defined an expression (s.department_id) for a DISTINCT ON clause to ORDER the result set in the above statement.

Output:

PostgreSQL Multiple Tables

Conclusion

We hope from the above article you have understood how to use PostgreSQL SELECT DISTINCT statement to return unique rows by removing duplicate rows from the result set.

Recommended Articles

This is a guide to PostgreSQL DISTINCT. Here we discuss Syntax and various examples of PostgreSQL DISTINCT with appropriate codes and output. You can also go through our other related articles to learn more –

  1. PostgreSQL GROUP BY
  2. PostgreSQL ORDER BY
  3. PostgreSQL Views | How to Create?
  4. The sequence in PostgreSQL | How to Work?

PostgreSQL Course (2 Courses, 1 Project)

2 Online Courses

1 Hands-on Project

7+ Hours

Verifiable Certificate of Completion

Lifetime Access

Learn More

0 Shares
Share
Tweet
Share
Primary Sidebar
PostgreSQL Tutorial
  • Basic
    • What is PostgreSQL
    • PostgreSQL Features
    • How to Install PostgreSQL
    • PostgreSQL Versions
    • PostgreSQL Architecture
    • PostgreSQL GUI
    • Postgres Command-Line
    • PostgreSQL Variables
    • PostgreSQL Data Types
    • PostgreSQL NOT NULL
    • PostgreSQL Integer
    • PostgreSQL Boolean
    • PostgreSQL BIGINT
    • PostgreSQL NULLIF
    • PostgreSQL Administration
    • PostgreSQL Commands
    • PostgreSQL Operators
    • PostgreSQL IN Operator
    • Postgres like query
    • PostgreSQL encode
    • PostgreSQL Cheat Sheet
    • PostgreSQL List Databases
    • PostgreSQL Rename Database
  • Control Statement
    • PostgreSQL IF Statement
    • PostgreSQL if else
    • PostgreSQL CASE Statement
    • PostgreSQL LOOP
    • PostgreSQL For Loop
    • PostgreSQL While Loop
  • Joins
    • Joins in PostgreSQL
    • PostgreSQL Inner Join
    • PostgreSQL Outer Join
    • LEFT OUTER JOIN in PostgreSQL
    • PostgreSQL FULL OUTER JOIN
    • PostgreSQL LEFT JOIN
    • PostgreSQL Full Join
    • PostgreSQL Cross Join
    • PostgreSQL NATURAL JOIN
    • PostgreSQL UPDATE JOIN
  • Queries
    • PostgreSQL Queries
    • PostgreSQL INSERT INTO
    • PostgreSQL WHERE Clause
    • PostgreSQL WITH Clause
    • PostgreSQL ORDER BY
    • PostgreSQL ORDER BY Random
    • PostgreSQL ORDER BY DESC
    • PostgreSQL GROUP BY
    • PostgreSQL group_concat
    • PostgreSQL HAVING
    • PostgreSQL Recursive Query
  • Advanced
    • PostgreSQL Schema
    • Postgres List Schemas
    • PostgreSQL Drop Schema
    • PostgreSQL VARCHAR
    • Array in PostgreSQL
    • PostgreSQL DDL
    • PostgreSQL List Users
    • Postgres Default User
    • Postgres add user
    • PostgreSQL User Password
    • PostgreSQL log_statement
    • PostgreSQL repository
    • PostgreSQL shared_buffer
    • PostgreSQL String Functions
    • PostgreSQL Compare Strings
    • PostgreSQL Text Search
    • PostgreSQL TEXT
    • PostgreSQL String Array
    • PostgreSQL where in array
    • PostgreSQL Constraints
    • PostgreSQL UNIQUE Constraint
    • PostgreSQL CHECK Constraint
    • PostgreSQL INTERSECT
    • PostgreSQL Like
    • Cursors in PostgreSQL
    • PostgreSQL UNION ALL
    • Indexes in PostgreSQL
    • PostgreSQL Index Types
    • PostgreSQL REINDEX
    • PostgreSQL UNIQUE Index
    • PostgreSQL Clustered Index
    • PostgreSQL DROP INDEX
    • PostgreSQL DISTINCT
    • PostgreSQL FETCH
    • PostgreSQL RAISE EXCEPTION
    • PostgreSQL Auto Increment
    • Sequence in PostgreSQL
    • Wildcards in PostgreSQL
    • PostgreSQL Subquery
    • PostgreSQL Alias
    • PostgreSQL LIMIT
    • PostgreSQL Limit Offset
    • PostgreSQL LAG()
    • PostgreSQL Table
    • Postgres Show Tables
    • PostgreSQL Describe Table
    • PostgreSQL Lock Table
    • PostgreSQL ALTER TABLE
    • Postgres Rename Table
    • PostgreSQL List Tables
    • PostgreSQL TRUNCATE TABLE
    • PostgreSQL Table Partitioning
    • Postgres DROP Table
    • PostgreSQL Functions
    • PostgreSQL Math Functions
    • PostgreSQL Window Functions
    • Aggregate Functions in PostgreSQL
    • PostgreSQL Primary Key
    • Foreign Key in PostgreSQL
    • PostgreSQL Procedures
    • PostgreSQL Stored Procedures
    • PostgreSQL Views
    • PostgreSQL Materialized Views
    • Postgres Create View
    • PostgreSQL Triggers
    • PostgreSQL DROP TRIGGER
    • PostgreSQL Date Functions
    • PostgreSQL TO_DATE()
    • PostgreSQL datediff
    • PostgreSQL Timestamp
    • PostgreSQL CURRENT_TIMESTAMP()
    • PostgreSQL Notify
    • PostgreSQL LENGTH()
    • PostgreSQL blob
    • PostgreSQL Median
    • PostgreSQL kill query
    • PostgreSQL Formatter
    • PostgreSQL RANK()
    • PostgreSQL Select
    • PostgreSQL Average
    • PostgreSQL DATE_PART()
    • PostgreSQL EXECUTE
    • PostgreSQL COALESCE
    • PostgreSQL EXTRACT()
    • PostgreSQL Sort
    • PostgreSQL TO_CHAR
    • PostgreSQL Interval
    • PostgreSQL Number Types
    • PostgreSQL ROW_NUMBER
    • Alter Column in PostgreSQL
    • PostgreSQL Identity Column
    • PostgreSQL SPLIT_PART()
    • PostgreSQL CONCAT()
    • PostgreSQL replace
    • PostgreSQL TRIM()
    • PostgreSQL MAX
    • PostgreSQL DELETE
    • PostgreSQL Float
    • PostgreSQL OID
    • PostgreSQL log
    • PostgreSQL REGEXP_MATCHES()
    • PostgreSQL MD5 
    • PostgreSQL NOW()
    • PostgreSQL RANDOM
    • PostgreSQL round
    • PostgreSQL Trunc()
    • PostgreSQL TIME
    • PostgreSQL IS NULL
    • PostgreSQL CURRENT_TIME
    • PostgreSQL MOD()
    • Postgresql Count
    • PostgreSQL Datetime
    • PostgreSQL MIN()
    • PostgreSQL age()
    • PostgreSQL enum
    • PostgreSQL OR
    • PostgreSQL Wal
    • PostgreSQL NOT IN
    • PostgreSQL SET
    • PostgreSQL Current Date
    • PostgreSQL Compare Date
    • PostgreSQL SERIAL
    • PostgreSQL UUID
    • PostgreSQL Merge
    • PostgreSQL Database
    • PostgreSQL Clone Database
    • PostgreSQL Copy Database
    • PostgreSQL Show Databases
    • PostgreSQL Restore Database
    • PostgreSQL DROP DATABASE
    • PostgreSQL ALTER DATABASE
    • Postgres DROP Database
    • Postgres Dump Database
    • PostgreSQL OFFSET
    • PostgreSQL GRANT
    • PostgreSQL COMMIT
    • PostgreSQL ROLLUP
    • PostgreSQL JSON
    • EXPLAIN ANALYZE in PostgreSQL
    • PostgreSQL Temporary Table
    • PostgreSQL Show Tables
    • PostgreSQL cluster
    • PostgreSQL Replication
    • PostgreSQL Logical Replication
    • PostgreSQL flush privileges
    • PostgreSQL Tablespaces
    • CAST in PostgreSQL
    • PostgreSQL CTE
    • hstore in PostgreSQL
    • PostgreSQL Encryption
    • PostgreSQL DECODE()
    • PostgreSQL Vacuum
    • PostgreSQL EXCLUDE
    • Postgres Change Password
    • Postgres Delete Cascade
    • PostgreSQL EXCEPT
    • PostgreSQL Roles
    • PostgreSQL Link
    • PostgreSQL Partition
    • PostgreSQL column does not exist
    • PostgreSQL Log Queries
    • PostgreSQL escape single quote
    • PostgreSQL Query Optimization
    • PostgreSQL Character Varying
    • PostgreSQL Transaction
    • PostgreSQL Extensions
    • PostgreSQL Import CSV
    • PostgreSQL Client
    • PostgreSQL caching
    • PostgreSQL Incremental Backup
    • PostgreSQL JSON vs JSONNB
    • PostgreSQL JDBC Driver
    • PostgreSQL Interview Questions
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Special Offer - PostgreSQL Course (2 Courses, 1 Project) Learn More