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:
Example 1: Single Column Distinct
Code:
-- Selects unique values from the "city" column in the "customers" table
SELECT DISTINCT city
FROM customers;
Explanation:
Example 2: Multiple Columns Distinct
Code:
-- Selects unique combinations of "city" and "country" in the "customers" table
SELECT DISTINCT city, country
FROM customers;
Explanation:
Example 3: Using DISTINCT with ORDER BY
Code:
-- Selects unique cities and orders them alphabetically
SELECT DISTINCT city
FROM customers
ORDER BY city;
Explanation:
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.
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4