PostgreSQL DISTINCT Keyword

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

When working with data, there may be situations where a table contains multiple duplicate records. When extracting such records, the DISTINCT keyword becomes particularly meaningful because it retrieves only unique records rather than duplicate ones.

Syntax

The basic syntax of the DISTINCT keyword for removing duplicate records is as follows:

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

Example

Create the COMPANY table (Download COMPANY SQL file), the data content is as follows:

exampledb# 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 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 is as follows:

 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 NAME values in the COMPANY table:

exampledb=# 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:

exampledb=# SELECT DISTINCT name FROM COMPANY;

The result is as follows:

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

From the results, you can see that duplicate data has been filtered out.