This article will be focusing on the use of SELECT statement with the DISTINCT clause to remove duplicates rows from a result set of query data.
Removing duplicate rows from a query result set in PostgreSQL can be done using the SELECT statement with the DISTINCT clause. It keeps one row for each group of duplicates. The DISTINCT clause can be used for a single column or for a list of columns.
Syntax:SELECT DISTINCT column_1 FROM table_name;
If you desire to operate on a list of columns the syntax will somewhat be like below:
Syntax:SELECT DISTINCT column_1, column_2, column_3 FROM table_name;
Now, let’s look into a few examples for better understanding. For the sake of example, we will create a sample database as explained below:
Create a database(say, Favourite_colours) using the commands shown below:
CREATE DATABASE Favourite_colours;
Now add a table(say, my_table) with columns(say, id, colour_1 and colour_2) to the database using the command below:
CREATE TABLE my_table(
id serial NOT NULL PRIMARY KEY,
colour_1 VARCHAR,
colour_2 VARCHAR
);
Now insert some data in the table that we just added to our database using the command below:
INSERT INTO my_table(colour_1, colour_2)
VALUES
('red', 'red'),
('red', 'red'),
('red', NULL),
(NULL, 'red'),
('red', 'green'),
('red', 'blue'),
('green', 'red'),
('green', 'blue'),
('green', 'green'),
('blue', 'red'),
('blue', 'green'),
('blue', 'blue');
Now check if everything is as intended by making a query as below:
SELECT
id,
colour_1,
colour_2
FROM
my_table;
If everything is as intended, the output will be like as shown below:

Since, our database is good to go, we move onto the implementation of the SELECT DISTINCT clause.
Example 1:
PostgreSQL DISTINCT on one column
SELECT
DISTINCT colour_1
FROM
my_table
ORDER BY
colour_1;
Output:

Example 2:
PostgreSQL DISTINCT on multiple columns
SELECT
DISTINCT colour_1,
colour_2
FROM
my_table
ORDER BY
colour_1,
colour_2;
Output:

Whether you're preparing for your first job interview or aiming to upskill in this ever-evolving tech landscape,
GeeksforGeeks Courses are your key to success. We provide top-quality content at affordable prices, all geared towards accelerating your growth in a time-bound manner. Join the millions we've already empowered, and we're here to do the same for you. Don't miss out -
check it out now!
Last Updated :
07 Oct, 2021
Like Article
Save Article