PostgreSQL SELECT DISTINCT

Summary: in this tutorial, you’ll learn how to use the PostgreSQL SELECT DISTINCT clause to retrieve unique values from a table.

Getting Started with the PostgreSQL SELECT DISTINCT clause #

The SELECT DISTINCT clause retrieves unique values from a table. Here’s the syntax of the SELECT DISTINCT clause:

SELECT DISTINCT column1
FROM table_name;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax, the SELECT DISTINCT clause uses the values in column1 of the table_name to evaluate the uniqueness of rows and return unique rows.

PostgreSQL SELECT DISTINCT clause example #

Suppose you have the following inventories table that includes product name, brand, warehouse, and quantity:

CREATE TABLE inventories(
   id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
   product_name VARCHAR(50) NOT NULL,
   brand VARCHAR(50) NOT NULL,
   warehouse VARCHAR(50) NOT NULL,
   quantity INT NOT NULL
);

INSERT INTO inventories (product_name, brand, warehouse, quantity) 
VALUES
('iPhone 16', 'Apple', 'San Jose', 50),
('iPhone 16', 'Apple', 'San Francisco', 30),
('iPhone 16 Pro', 'Apple', 'San Jose', 40),
('iPhone 16 Pro', 'Apple', 'San Francisco', 20),
('iPhone 16 Pro Max', 'Apple', 'San Francisco', 25),
('Galaxy S22', 'Samsung', 'San Jose', 50),
('Galaxy S22', 'Samsung', 'San Francisco', 30),
('Galaxy S22 Ultra', 'Samsung', 'San Francisco', 20),
('Galaxy Z Fold 4', 'Samsung', 'San Jose', 55),
('Galaxy Z Fold 4', 'Samsung', 'San Francisco', 35);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The following example uses a SELECT statement to retrieve all product names from the inventories table:

SELECT
  product_name
FROM
  inventories
ORDER BY
  product_name;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

   product_name
-------------------
 Galaxy S22
 Galaxy S22
 Galaxy S22 Ultra
 Galaxy Z Fold 4
 Galaxy Z Fold 4
 iPhone 16
 iPhone 16
 iPhone 16 Pro
 iPhone 16 Pro
 iPhone 16 Pro MaxCode language: plaintext (plaintext)

The result sets include many duplicate product names.

To remove the duplicate names from the result set, you can use the SELECT DISTINCT clause:

SELECT DISTINCT
  product_name
FROM
  inventories
ORDER BY
  product_name;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

   product_name
-------------------
 Galaxy S22
 Galaxy S22 Ultra
 Galaxy Z Fold 4
 iPhone 16
 iPhone 16 Pro
 iPhone 16 Pro MaxCode language: plaintext (plaintext)

Applying the SELECT DISTINCT clause to multiple columns #

The SELECT DISTINCT clause can accept multiple columns.

Here’s the syntax for using the SELECT DISTINCT clause with two columns:

SELECT DISTINCT column1, column2
FROM table_name;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this case, the SELECT DISTINCT clause uses the combination of values in these columns to evaluate the duplicates.

For example, the following statement uses the SELECT DISTINCT clause to retrieve the distinct product name and brand from the inventories table:

SELECT DISTINCT product_name, brand
FROM inventories;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

   product_name    |  brand
-------------------+---------
 iPhone 16         | Apple
 iPhone 16 Pro Max | Apple
 Galaxy S22 Ultra  | Samsung
 iPhone 16 Pro     | Apple
 Galaxy S22        | Samsung
 Galaxy Z Fold 4   | SamsungCode language: plaintext (plaintext)

The output shows that the SELECT DISTINCT clause returns a unique product names and brands.

SELECT DISTINCT vs. GROUP BY clause #

The SELECT DISTINCT retrieves unique rows from a table by eliminating the duplicate rows from the result set.

The GROUP BY 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 clause without an aggregate function has the same effect as the SELECT DISTINCT clause.

For example, the following statement uses the GROUP BY clause to select distinct product names from the inventories table:

SELECT
  product_name
FROM
  inventories
GROUP BY
  product_name
ORDER BY
  product_name;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

   product_name
-------------------
 Galaxy S22
 Galaxy S22 Ultra
 Galaxy Z Fold 4
 iPhone 16
 iPhone 16 Pro
 iPhone 16 Pro MaxCode language: plaintext (plaintext)

It returns the same result set as the following query that uses the DISTINCT clause:

SELECT DISTINCT
  product_name
FROM
  inventories
ORDER BY
  product_name;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Summary #

• Use the SELECT DISTINCT clause to retrieve unique rows from a table.

Quiz #