{"id":653,"date":"2024-12-05T10:58:35","date_gmt":"2024-12-05T03:58:35","guid":{"rendered":"https:\/\/www.pgtutorial.com\/?page_id=653"},"modified":"2025-01-02T19:59:13","modified_gmt":"2025-01-02T12:59:13","slug":"postgresql-select-distinct","status":"publish","type":"page","link":"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-select-distinct\/","title":{"rendered":"PostgreSQL SELECT DISTINCT"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you’ll learn how to use the PostgreSQL SELECT DISTINCT<\/code> clause to retrieve unique values from a table.<\/p>\n\n\n\n

Getting Started with the PostgreSQL SELECT DISTINCT clause #<\/a><\/h2>\n\n\n\n

The SELECT DISTINCT<\/code> clause retrieves unique values from a table. Here’s the syntax of the SELECT DISTINCT<\/code> clause:<\/p>\n\n\n

SELECT<\/span> DISTINCT<\/span> column1\nFROM<\/span> table_name<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, the SELECT DISTINCT<\/code> clause uses the values in column1<\/code> of the table_name<\/code> to evaluate the uniqueness of rows and return unique rows.<\/p>\n\n\n\n
PostgreSQL SELECT DISTINCT clause example #<\/a><\/h2>\n\n\n\n
Suppose you have the following inventories<\/code> table that includes product name, brand, warehouse, and quantity:<\/p>\n\n\n\n
SQL Script for creating the inventories table and inserting data into it<\/summary>
CREATE<\/span> TABLE<\/span> inventories(\n   id INT<\/span> GENERATED<\/span> ALWAYS<\/span> AS<\/span> IDENTITY<\/span> PRIMARY KEY<\/span>,\n   product_name VARCHAR<\/span>(50<\/span>) NOT<\/span> NULL<\/span>,\n   brand VARCHAR<\/span>(50<\/span>) NOT<\/span> NULL<\/span>,\n   warehouse VARCHAR<\/span>(50<\/span>) NOT<\/span> NULL<\/span>,\n   quantity INT<\/span> NOT<\/span> NULL<\/span>\n);\n\nINSERT<\/span> INTO<\/span> inventories (product_name, brand, warehouse, quantity) \nVALUES<\/span>\n('iPhone 16'<\/span>, 'Apple'<\/span>, 'San Jose'<\/span>, 50<\/span>),\n('iPhone 16'<\/span>, 'Apple'<\/span>, 'San Francisco'<\/span>, 30<\/span>),\n('iPhone 16 Pro'<\/span>, 'Apple'<\/span>, 'San Jose'<\/span>, 40<\/span>),\n('iPhone 16 Pro'<\/span>, 'Apple'<\/span>, 'San Francisco'<\/span>, 20<\/span>),\n('iPhone 16 Pro Max'<\/span>, 'Apple'<\/span>, 'San Francisco'<\/span>, 25<\/span>),\n('Galaxy S22'<\/span>, 'Samsung'<\/span>, 'San Jose'<\/span>, 50<\/span>),\n('Galaxy S22'<\/span>, 'Samsung'<\/span>, 'San Francisco'<\/span>, 30<\/span>),\n('Galaxy S22 Ultra'<\/span>, 'Samsung'<\/span>, 'San Francisco'<\/span>, 20<\/span>),\n('Galaxy Z Fold 4'<\/span>, 'Samsung'<\/span>, 'San Jose'<\/span>, 55<\/span>),\n('Galaxy Z Fold 4'<\/span>, 'Samsung'<\/span>, 'San Francisco'<\/span>, 35<\/span>);<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre><\/details>\n\n\n\n
id<\/th>product_name<\/th>brand<\/th>warehouse<\/th>quantity<\/th><\/tr><\/thead>
1<\/td>iPhone 16<\/td>Apple<\/td>San Jose<\/td>50<\/td><\/tr>
2<\/td>iPhone 16<\/td>Apple<\/td>San Francisco<\/td>30<\/td><\/tr>
3<\/td>iPhone 16 Pro<\/td>Apple<\/td>San Jose<\/td>40<\/td><\/tr>
4<\/td>iPhone 16 Pro<\/td>Apple<\/td>San Francisco<\/td>20<\/td><\/tr>
5<\/td>iPhone 16 Pro Max<\/td>Apple<\/td>San Francisco<\/td>25<\/td><\/tr>
6<\/td>Galaxy S22<\/td>Samsung<\/td>San Jose<\/td>50<\/td><\/tr>
7<\/td>Galaxy S22<\/td>Samsung<\/td>San Francisco<\/td>30<\/td><\/tr>
8<\/td>Galaxy S22 Ultra<\/td>Samsung<\/td>San Francisco<\/td>20<\/td><\/tr>
9<\/td>Galaxy Z Fold 4<\/td>Samsung<\/td>San Jose<\/td>55<\/td><\/tr>
10<\/td>Galaxy Z Fold 4<\/td>Samsung<\/td>San Francisco<\/td>35<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
The following example uses a SELECT<\/a><\/code> statement to retrieve all product names from the inventories<\/code> table:<\/p>\n\n\n
SELECT<\/span>\n  product_name\nFROM<\/span>\n  inventories\nORDER<\/span> BY<\/span>\n  product_name;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
   product_name\n-------------------\n Galaxy S22\n Galaxy S22\n Galaxy S22 Ultra\n Galaxy Z Fold 4\n Galaxy Z Fold 4\n iPhone 16\n iPhone 16\n iPhone 16 Pro\n iPhone 16 Pro\n iPhone 16 Pro Max<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
The result sets include many duplicate product names.<\/p>\n\n\n\n
To remove the duplicate names from the result set, you can use the SELECT DISTINCT<\/code> clause:<\/p>\n\n\n
SELECT<\/span> DISTINCT<\/span>\n  product_name\nFROM<\/span>\n  inventories\nORDER<\/span> BY<\/span>\n  product_name;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
   product_name\n-------------------\n Galaxy S22\n Galaxy S22 Ultra\n Galaxy Z Fold 4\n iPhone 16\n iPhone 16 Pro\n iPhone 16 Pro Max<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
Applying the SELECT DISTINCT clause to multiple columns #<\/a><\/h2>\n\n\n\n
The SELECT DISTINCT<\/code> clause can accept multiple columns. <\/p>\n\n\n\n
Here’s the syntax for using the SELECT DISTINCT<\/code> clause with two columns:<\/p>\n\n\n
SELECT<\/span> DISTINCT<\/span> column1, column2\nFROM<\/span> table_name<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this case, the SELECT DISTINCT<\/code> clause uses the combination of values in these columns to evaluate the duplicates.<\/p>\n\n\n\n
For example, the following statement uses the SELECT DISTINCT<\/code> clause to retrieve the distinct product name and brand from the inventories<\/code> table:<\/p>\n\n\n
SELECT<\/span> DISTINCT<\/span> product_name, brand\nFROM<\/span> inventories;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
   product_name    |  brand\n-------------------+---------\n iPhone 16         | Apple\n iPhone 16 Pro Max | Apple\n Galaxy S22 Ultra  | Samsung\n iPhone 16 Pro     | Apple\n Galaxy S22        | Samsung\n Galaxy Z Fold 4   | Samsung<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
The output shows that the SELECT DISTINCT<\/code> clause returns a unique product names and brands.<\/p>\n\n\n\n
SELECT DISTINCT vs. GROUP BY clause #<\/a><\/h2>\n\n\n\n
The SELECT DISTINCT<\/code> retrieves unique rows from a table by eliminating the duplicate rows from the result set.<\/p>\n\n\n\n
The GROUP BY<\/a><\/code> clause groups rows based on the values of one or more columns into groups and apply an aggregate function to each group. The GROUP BY<\/code> clause without an aggregate function has the same effect as the SELECT DISTINCT<\/code> clause.<\/p>\n\n\n\n
For example, the following statement uses the GROUP BY<\/code> clause to select distinct product names from the inventories table:<\/p>\n\n\n
SELECT<\/span>\n  product_name\nFROM<\/span>\n  inventories\nGROUP<\/span> BY<\/span>\n  product_name\nORDER<\/span> BY<\/span>\n  product_name;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
   product_name\n-------------------\n Galaxy S22\n Galaxy S22 Ultra\n Galaxy Z Fold 4\n iPhone 16\n iPhone 16 Pro\n iPhone 16 Pro Max<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
It returns the same result set as the following query that uses the DISTINCT<\/code> clause:<\/p>\n\n\n
SELECT<\/span> DISTINCT<\/span>\n  product_name\nFROM<\/span>\n  inventories\nORDER<\/span> BY<\/span>\n  product_name;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n