PostgreSQL DISTINCT Clause: Retrieve Unique Values from Queries

Using the DISTINCT Clause in PostgreSQL

The DISTINCT clause in PostgreSQL is used to return unique values from a column or combination of columns within a query. This feature is helpful when you need to eliminate duplicate results and retrieve only unique data entries, improving query precision and reducing redundancy.

Syntax:

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

Explanation:

• column1, column2, ...: Columns from which you want to fetch distinct values.
• table_name: The name of the table from which data is retrieved.

Example 1: Single Column Distinct

Code:

-- Selects unique values from the "city" column in the "customers" table
SELECT DISTINCT city
FROM customers;

Explanation:

• This query will return a list of unique cities from the city column in the customers table, eliminating any duplicate city names.

Example 2: Multiple Columns Distinct

Code:

-- Selects unique combinations of "city" and "country" in the "customers" table
SELECT DISTINCT city, country
FROM customers;

Explanation:

• Using multiple columns with DISTINCT, this query retrieves unique pairs of city and country. If multiple rows have the same city and country combination, only one instance will be shown in the result.

Example 3: Using DISTINCT with ORDER BY

Code:

-- Selects unique cities and orders them alphabetically
SELECT DISTINCT city
FROM customers
ORDER BY city;

Explanation:

• Here, the DISTINCT clause is used with the ORDER BY clause to sort the unique cities alphabetically.

Notes:

1. Distinctness in Multiple Columns

When you use DISTINCT with multiple columns, the clause considers each combination of column values as a unique row. For instance, (city, country) will only consider rows unique if both values in the pair are unique together.

2. DISTINCT and Performance

Applying DISTINCT can impact query performance, especially in large datasets, as the database needs to evaluate each row for uniqueness. Use it wisely to avoid unnecessary overhead.

3. Alternatives

In some cases, using GROUP BY can achieve similar results with the added benefit of aggregate functions like COUNT, SUM, etc. Consider using GROUP BY if your query also requires aggregation.

Summary:

The DISTINCT clause is a powerful tool in PostgreSQL for filtering unique values in result sets. It’s commonly used for deduplication in columns or combinations of columns and can be combined with other clauses like ORDER BY.

All PostgreSQL Questions, Answers, and Code Snippets Collection.