PostgreSQL DISTINCT Keyword

In PostgreSQL, the DISTINCT keyword is used with the SELECT statement to remove duplicate records and retrieve only unique ones.

When operating on data, it's possible to encounter a situation where a table contains multiple duplicate records. When extracting such records, the DISTINCT keyword becomes particularly meaningful, as it retrieves each record only once, rather than multiple times.

Syntax

The basic syntax for the DISTINCT keyword to remove duplicate records is as follows:

SELECT DISTINCT column1, column2,.....columnN
FROM table_name
WHERE [condition]

Example

Create the COMPANY table (download COMPANY SQL file), with the following data content:

tutorialprodb# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

Let's insert two more records:

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (8, 'Paul', 32, 'California', 20000.00 );

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (9, 'Allen', 25, 'Texas', 15000.00 );

Now the data looks like this:

id | name  | age | address    | salary
----+-------+-----+------------+--------
  1 | Paul  |  32 | California |  20000
  2 | Allen |  25 | Texas      |  15000
  3 | Teddy |  23 | Norway     |  20000
  4 | Mark  |  25 | Rich-Mond  |  65000
  5 | David |  27 | Texas      |  85000
  6 | Kim   |  22 | South-Hall |  45000
  7 | James |  24 | Houston    |  10000
  8 | Paul  |  32 | California |  20000
  9 | Allen |  25 | Texas      |  15000
(9 rows)

Next, let's find all the names in the COMPANY table:

tutorialprodb=# SELECT name FROM COMPANY;

The result is as follows:

name
-------
 Paul
 Allen
 Teddy
 Mark
 David
 Kim
 James
 Paul
 Allen
(9 rows)

Now, let's use the DISTINCT clause in the SELECT statement:

tutorialprodb=# SELECT DISTINCT name FROM COMPANY;

The result is as follows:

name
-------
 Teddy
 Paul
 Mark
 David
 Allen
 Kim
 James
(7 rows)

As seen from the result, the duplicate data has been filtered out.