DISTINCT Clause in PostgreSQL

The DISTINCT Clause responsible to remove duplicate rows from the SELECT statement's result set. The DISTINCT Clause keeps one record for each group of duplicates.

Syntax of DISTINCT Clause in PostgreSQL


SELECT
   DISTINCT Col_1
FROM
   table_name;


Notes:
1- The DISTINCT clause can be applied to one or more columns of a table.
2- In syntax, the values of column Col_1 are used to evaluate duplicates. It can also be applied to multiple columns. If you specify the columns in the SELECT statement, the DISTINCT clause will evaluate duplicates based on a combination of the values of these columns. 

Example: Suppose we have a table named EMPLOYEE.


CREATE TABLE employee (
    empid SERIAL,
    fname VARCHAR NOT NULL,
                lname VARCHAR NOT NULL
);

-- Inserted some record using the following query
INSERT INTO employee(fname, lname)
VALUES('Ashish', 'Singh');

INSERT INTO employee(fname, lname)
VALUES('Naina', 'Singh');

INSERT INTO employee(fname, lname)
VALUES('Sohan', 'Chaurasia');

INSERT INTO employee(fname, lname)
VALUES('Virendra', 'Gupta');

INSERT INTO employee(fname, lname)
VALUES('Ashish', 'Gupta');

INSERT INTO employee(fname, lname)
VALUES('Sohan', 'Maddheshiya');

INSERT INTO employee(fname, lname)
VALUES('Naina', 'Singh');


 Fetching data from EMPLOYEE table without DISTINCT Clause.

SELECT empid,fname,lname FROM employee;


Result:

PostgreSQL DISTINCT on one column example 
The following query will demonstrate you DISTINCT on single column.

SELECT DISTINCT fname FROM employee;


Result:

PostgreSQL DISTINCT on multiple columns

The following query will demonstrate you DISTINCT on multiple column.

SELECT DISTINCT fname,lname FROM employee;


Result:

As we know, DISTINCT on multiple columns evaluate duplicates based on a combination of the values of these columns, in the EMPLOYEE table 'Naina', 'Singh' will be duplicate row for columns fname and lname.

“DISTINCT ON” in PostgreSQL

PostgreSQL is also provide the DISTINCT ON(expression), using this one we can keep the first row of each group of duplicates. See the following syntax

SELECT
   DISTINCT ON (Col_1) column_alias,
   Col_2
FROM
   table_name
ORDER BY
   Col_1,
   Col_2;

  
The order of the result set returned by the SELECT statement is not predictable therefore the first row of the duplicate group is also unpredictable, so it will be good practice if you are using ORDER BY Clause with DISTINCT ON(Expression) to make clear result set.

SELECT DISTINCT ON(fname) fname, lname
  FROM employee
  ORDER BY fname, lname


Result:


The real table value is as below


After SELECT query with DISTINCT ON the rows selected as below, red marks highlighted has been selected in query.
In this tutorial, you have learned how to use PostgreSQL SELECT DISTINCT statement to remove duplicate rows from the result set returned by a query.

PostgreSQL: Function and its uses

The function is the set of PostgreSQL statements that stored on the database server and can be invoked using the SQL interface. PostgreSQL ...