PostgreSQL DISTINCT

In this section, we are going to understand the working of the PostgreSQL DISTINCT clause, which is used to delete the matching rows or data from a table and get only the unique records.

Note: The DISTINCT clause is only used with the SELECT command.The DISTINCT clause can be useful to several columns in the select list of the SELECT command, and also preserves one row for each duplicate group.

Syntax of the PostgreSQL select distinct clause

The basic syntaxes of the DISTINCT clause are as follows:

Syntax1

In the below syntax, the values in column1 are used to assess the duplicate.

If we describe the various columns, the DISTINCT clause will analyze the matching rows or data according to the grouping of these column’s values.

Select Distinct column1  

FROM table_name;

Syntax2

If both column1 and column2 columns have the similar values, then to get the duplicate values, we can use the below syntax:

SELECT DISTINCT column1, column2  

FROM table_name;

Syntax3

PostgreSQL also provides the DISTINCT ON expression to maintain the first row of each group of duplicates. So, for these conditions, the below command can be used:

SELECT DISTINCT ON (column1) column_alias, column2  

FROM table_name  

ORDER BY column1, column2 ;

If we continuously perform the ORDER BY clause with the DISTINCT ON (expression) to make the outcome expectable as it is an excellent exercise to perform.

Note: The leftmost expression in the ORDER BY clause must match the DISTINCT ON expression.

Examples of PostgreSQL SELECT DISTINCT

To understand the working of the DISTINCT clause in PostgreSQL, we will see some examples.

For this, we are creating a new table named demo_dist and inserting some values into a particular table. In this section, we are just performing the command in psql or pgAdmin.

To see the example of the Select Distinct command in PostgreSQL ‘s Pgadmin4, we need to follow the below steps:

Step1

We will create one table with the help of the CREATE TABLE command, as we can see in the below command that we have created the Demo_dist table, which consists of three columns Serial_NO, Summer_fruits, and Winter_fruits.

CREATE TABLE demo_dist(  

    Serial_No serial NOT NULL PRIMARY KEY,  

    Summer_fruits VARCHAR,  

    Winter_fruits VARCHAR     

);

Output

After executing the above command, we will get the below message window; the Demo_dist table has been created successfully.

Step2

After creating the Demo_dist table, we are going to insert some values into it with the help of below INSERT command:

INSERT INTO demo_dist (Summer_fruits, Winter_fruits)  

VALUES('Mango', 'Grape'),  

('Watermelon', 'Pears'),('Apples','Apples'),   

('Mango', NULL),(NULL, 'Mango'), ('Apples','Apples'),  

('Guava', 'Oranges'),('Pineapple', 'Pineapple'),  

('Musk Melon', ' Bananas'),('Litchi', 'Cranberries');

Output

After executing the above command, we will get the below message window; the values have been inserted successfully into the Demo_dist table.

Step3

After that, we will select the data from the Demo_dist table with the help of below SELECT command:

SELECT Serial_No,Summer_fruits, Winter_fruits  

FROM demo_dist ;

Output

Once we implemented the above command, we will get the below output:

Examples of PostgreSQL DISTINCT one column

In the below example, we will select unique records in the Summer_fruits column from the Demo_dist table and perform the ORDER BY clause to get the result in sequential order.

SELECT DISTINCT Summer_fruits  

FROM demo_dist   

ORDER BY Summer_fruits ;

Output

After implementing the above command, we will get the below output, where we get the list of summer _fruits column in the alphabetic order.

Example of multiple columns using PostgreSQL DISTINCT

In the below example, we will use the DISTINCT clause on one or more columns,

To fetch the values of various columns using distinct clause, we will take the Summer_fruits and Winter_fruits columns as we can see in the following command:

SELECT DISTINCT Summer_fruits, Winter_fruits  

FROM demo_dist  

ORDER BY Summer_fruits, Winter_fruits;

Output

After implementing the above command, we will get the below output where we get the list of summer _fruits, and the Winter_fruits columns in the alphabetic order.

In the above command, we describe both Summer_fruits and Winter_fruits columns in the SELECT DISTINCT clause; that’s why PostgreSQL joined the values in both Summer_fruits and Winter_fruits columns for analyzing the uniqueness of the rows.

Once we perform the above command, it returns the unique combination of Summer_fruits and Winter_fruits from the Demo_dist table.

Note: The Demo_dist table has two rows with the value Apples in both Summer_fruits and Winter_fruits columns. And when we used the DISTINCT for both columns, one row was deleted from the output because it is the duplicate.

Example PostgreSQL DISTINCT ON

In the below example, we will sort the outcome by the Summer_fruits and Winter_fruits, and it keeps the first row in the returned output for every group of duplicates value.

SELECT Distinct ON (Summer_fruits) summer_fruits, winter_fruits  

FROM public.demo_dist  

ORDER BY Summer_fruits, winter_fruits;

Output

After executing the above command, we will get the below output, which displays the result in which both the columns, i.e., Summer_fruits, and winter_fruits do not display the similar values.