PostgreSQL – SELECT DISTINCT clause
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:
Contact Us