PostgreSQL SELECT DISTINCT By Practical Examples | A 101 Guide

PostgreSQL is a powerful object-relational database system. Having been in active development, for now, almost 30 years, PostgreSQL is known for its reliability, data integrity, performance, and robustness.

In this article, you will gain information about PostgreSQL DISTINCT Clause. You will also gain a holistic understanding of PostgreSQL, its key features, different syntax, and examples of PostgreSQL Clause.

Read along to find out in-depth information about creating PostgreSQL DISTINCT Clause.

Table of Contents

• What is PostgreSQL?
  • Key Features of PostgreSQL
• What is PostgreSQL DISTINCT Clause?
• How to use PostgreSQL Distinct Clause?
• Examples of PostgreSQL DISTINCT Clause
• Conclusion

What is PostgreSQL?

PostgreSQL is a high-performance, open-source relational database. It is famous for its Open-Source platform, which supports all RDBMS functionalities. In addition to RDBMS capabilities, it provides indexes, views, stored procedures, triggers, atomicity features, and so on. SQL and JSON queries are also supported. PostgreSQL is also capable of serving as a data warehouse system.

PostgreSQL is compatible with a wide range of operating systems, including Windows, Linux, macOS, and UNIX. The source code for PostgreSQL is publicly accessible under an open-source license, letting users use, modify, and implement it as needed. Since PostgreSQL offers no license costs, there is no risk of over-deployment, which saves a lot of expenses.

Key Features of PostgreSQL

Some of the key features of PostgreSQL are as follows:

• Customizable: PostgreSQL can be altered and customized by writing plugins to make the DBMS meet your needs. It also lets you integrate custom functions written in other programming languages like Java, C, C++, and others.
• Community Support: A professional community is always available to its users. PostgreSQL also has a large range of private, third-party support services available.
• Open-Source: As a free and open-source solution, PostgreSQL offers Object-Oriented and Relational Database capabilities.
• Users: It is a well-known and commonly used RDBMS. Apple, Etsy, Facebook, Instagram, Red Hat, Skype, Spotify, and many other companies utilize PostgreSQL.
• Code quality: Every line of code in PostgreSQL is evaluated by a team of professionals, and the whole development process is driven by the community, allowing for speedy bug reporting, changes, and verification.
• Data Availability and Resiliency: PostgreSQL versions that are privately supported provide additional high availability, resilience, and security for mission-critical production settings such as government agencies, financial institutions, and healthcare providers.

What is PostgreSQL DISTINCT Clause?

In the SELECT statement, the PostgreSQL DISTINCT clause is used to delete duplicate records from a result set. For each set of duplicates, the DISTINCT clause saves one row. PostgreSQL DISTINCT eliminates all duplicate rows and keeps just one entry for each duplicated row group. The DISTINCT clause can also be applied to multiple columns of a single table,  on one or more columns of different tables attached through joins. 

According to the PostgreSQL Documentation, “If SELECT DISTINCT is specified, all duplicate rows are removed from the result set (one row is kept from each group of duplicates).“

1) Syntax

SELECT DISTINCT | DISTINCT ON (distinct_expressions_id)
column
FROM tables
[WHERE conds];

2) Parameters

• distinct_expressions_id: This represents the expressions that are used to delete duplicates.
• column: This represents the name(s) of the column(s) which are to be evaluated for unique values.
• tables: This represents the name(s) of the table(s) from which you want to get the records. The FROM operator must specify at least one table.
• WHERE conds: <Optional> This represents the conditions to be met for the records to be selected.

How to use the PostgreSQL DISTINCT Clause?

The different uses of PostgreSQL DISTINCT Clause are as follows:

Syntax #1

SELECT
DISTINCT column_name1
FROM
table_name;

In this case, the PostgreSQL DISTINCT clause is used for the column_name1 which evaluates the duplicate rows according to the column_name1 column of the table_name. Thus, the output will only show the list of unique column_name1 values.

Syntax #2

SELECT
DISTINCT column_name1, column_name2
FROM
table_name;

In this case, the PostgreSQL DISTINCT clause is applied to a combination of column names of a table. It checks for unique values for the combination (union) of the columns in that particular table. In the above statement, the PostgreSQL DISTINCT Clause evaluates and displays the output for the combination of values of column_name1 and column_name2.

Syntax #3

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

In this case, the DISTINCT ON expression is similar to PostgreSQL DISTINCT clause. It is used with the SELECT statement to remove duplicates from a query set result. In addition, it maintains only the “first row” of each duplicate row in the query set result.

In this case, the firstmost row of every group is random. ORDER BY clause is used along with the PostgreSQL DISTINCT On clause to make the resulting set look organized.

To have in-depth information about the PostgreSQL DISTINCT On Clause, you can have a look at the example of the PostgreSQL DISTINCT On clause.

Examples of PostgreSQL DISTINCT Clause

For showcasing different example use cases of PostgreSQL DISTINCT clause, you can consider the following table.

The different examples are as follows:

• PostgreSQL DISTINCT Clause: Single Column
• PostgreSQL DISTINCT Clause: Multiple Columns
• PostgreSQL DISTINCT Clause: With DISTINCT ON Clause
• PostgreSQL DISTINCT Clause: Multiple Tables

1) PostgreSQL DISTINCT Clause: Single Column

SELECT
DISTINCT fore_color
FROM
ColorProperties
ORDER BY
fore_color;

In this case, the PostgreSQL DISTINCT Clause is used to fetch the unique values of the fore_color column from the ColorProperties table. And to sort the output of the query set in ascending order, the Order By clause is used. 

Output:

2) PostgreSQL DISTINCT Clause: Multiple Columns

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

In this case, the PostgreSQL SELECT DISTINCT Clause is used along with 2 columns i.e., fore_color and back_color. So, it evaluates the unique values for the combination of the values in the 2 columns.

Output:

3) PostgreSQL DISTINCT Clause: With DISTINCT ON Clause

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

In this case, the PostgreSQL DISTINCT On clause evaluates the unique values and removes all duplicate rows for the first column keeping a one-row entry for each value of the first column.

Output:

4) PostgreSQL DISTINCT Clause: Multiple Tables

To better understand the application of PostgreSQL DISTINCT Clause in multiple tables, let’s consider two tables i.e, students and departments.

Creating students table & Inserting Values:

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');

Creating departments tables & inserting Values:

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');

Query for evaluating unique values:

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

In this case, first, both the tables are joined based on the join condition. Then, the PostgreSQL DISTINCT On clause is applied to remove duplicate entries and keep a one-row entry for each value of the first column.

Output:

Conclusion

In this article, you learned about PostgreSQL DISTINCT Clause. This article also focused on PostgreSQL, its key features, different examples, and use cases of the PostgreSQL DISTINCT Clause.

Hevo Data, a No-code Data Pipeline provides you with a consistent and reliable solution to manage data transfer between a variety of sources and a wide variety of Desired Destinations with a few clicks.

Visit our Website to Explore Hevo[/hevoButton]

Hevo Data with its strong integration with 150+ Data Sources (including 40+ Free Sources) such as PostgreSQL allows you to not only export data from your desired data sources & load it to the destination of your choice but also transform & enrich your data to make it analysis-ready. Hevo also allows the integration of data from non-native sources using Hevo’s in-built REST API & Webhooks Connector. You can then focus on your key business needs and perform insightful analysis using BI tools. 

Want to give Hevo a try? Sign Up[/hevoButton] for a 14-day free trial and experience the feature-rich Hevo suite first hand. You may also have a look at the amazing price, which will assist you in selecting the best plan for your requirements.

Share your experience of understanding the usage of PostgreSQL DISTINCT Clause in the comment section below! We would love to hear your thoughts.