PostgreSQL: Retrieving Unique Records with DISTINCT

Eliminate duplicate rows from query results using PostgreSQL's DISTINCT clause. This guide explains the syntax and usage of DISTINCT.

Retrieving Unique Records in PostgreSQL with `DISTINCT`

Understanding the `DISTINCT` Clause

In PostgreSQL, the `DISTINCT` clause is used in `SELECT` statements to retrieve only unique rows from a table. It removes duplicate rows from the result set, ensuring that you get only unique combinations of values for the specified columns. This is helpful for tasks such as data analysis, reporting, or any scenario where you only need unique records.

`DISTINCT` Clause Syntax

The basic syntax for using `DISTINCT` is:

1. `DISTINCT` with a Single Column

SELECT DISTINCT column_name
FROM table_name;

This selects only the unique values from the specified column.

2. `DISTINCT` with Multiple Columns

SELECT DISTINCT column1, column2, ...
FROM table_name;

This selects only the unique combinations of values from the specified columns.

3. `DISTINCT ON`

SELECT DISTINCT ON (column1) column_alias, column2
FROM table_name
ORDER BY column1, column2;

This returns the first row of each group of duplicates, based on the `ORDER BY` clause. The expression in the `ORDER BY` clause must start with the same expression that is in `DISTINCT ON`.

Examples: Using `DISTINCT`

These examples use the `DISTINCT` clause with a sample table named `demo_dist`. You would need to create this table and populate it with data to see the results. The `ORDER BY` clause is used to sort results for better readability.

1. Creating and Populating the Table

CREATE TABLE demo_dist (
  Serial_No SERIAL PRIMARY KEY,
  Summer_fruits VARCHAR,
  Winter_fruits VARCHAR
);

INSERT INTO demo_dist (Summer_fruits, Winter_fruits) VALUES
('Mango', 'Grape'),
('Watermelon', 'Pears'),
('Apples', 'Apples'),
('Mango', NULL),
('Apples', 'Apples'),
('Guava', 'Oranges');

2. `DISTINCT` with One Column

SELECT DISTINCT Summer_fruits
FROM demo_dist
ORDER BY Summer_fruits;

3. `DISTINCT` with Multiple Columns

SELECT DISTINCT Summer_fruits, Winter_fruits
FROM demo_dist
ORDER BY Summer_fruits, Winter_fruits;

4. `DISTINCT ON`

SELECT DISTINCT ON (Summer_fruits) Summer_fruits, Winter_fruits
FROM demo_dist
ORDER BY Summer_fruits, Winter_fruits;

Conclusion

The `DISTINCT` clause in PostgreSQL is a valuable tool for retrieving unique rows from a table. Understanding its various forms allows you to efficiently manage and analyze data, focusing on unique values rather than duplicates.