PostgreSQL DISTINCT Keyword

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

When we operate data in our daily lives, there may be a situation where multiple duplicate records exist in a table. When extracting such records, the DISTINCT keyword is particularly meaningful, as it only retrieves unique records rather than duplicate ones.

Syntax

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

SELECT DISTINCT column1, 'column',2,.....columnN
FROM table_name
WHERE [condition]

Online example

Create COMPANY table (Download COMPANY SQL file ），data content as follows：

w3codeboxdb# 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 rows of data:

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 );

The data is now 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, we find all the NAMEs in the COMPANY table:

w3codeboxdb=# SELECT name FROM COMPANY;

The results are as follows:

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

Now we use the DISTINCT clause in the SELECT statement:

w3codeboxdb=# SELECT DISTINCT name FROM COMPANY;

The results are as follows:

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

As can be seen from the results, duplicate data has been deleted.