Open In App

PostgreSQL – SELECT DISTINCT clause

Improve
Improve
Like Article
Like
Save
Share
Report

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: 
 

 


Last Updated : 07 Oct, 2021
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads