Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://www.geeksforgeeks.org/postgresql/postgresql-select-distinct-clause/ below:

PostgreSQL - SELECT DISTINCT clause

Last Updated : 12 Jul, 2025

The SELECT statement with the DISTINCT clause to remove duplicate rows from a query result set in PostgreSQL. By leveraging the DISTINCT clause, you can ensure your query results contain only unique rows, whether you're dealing with a single column or multiple columns.

The DISTINCT clause in PostgreSQL is used to return unique rows from a result set. When used, it keeps only one row for each group of duplicates, effectively eliminating any duplicate rows in the output.

Let us better understand the SELECT DISTINCT Clause in PostgreSQL to better understand the concept.

SELECT DISTINCT column_1 FROM table_name;

SELECT DISTINCT column_1, column_2, column_3 FROM table_name;

Now, let's look into a few examples for better understanding. For the sake of example, we will create a sample database (say, Favourite_colours) using the commands shown below.
 

CREATE DATABASE Favourite_colours;
CREATE TABLE my_table(
    id serial NOT NULL PRIMARY KEY,
    colour_1 VARCHAR,
    colour_2 VARCHAR
);
INSERT INTO my_table(colour_1, colour_2)
VALUES
    ('red', 'red'),
    ('red', 'red'),
    ('red', NULL),
    (NULL, 'red'),
    ('red', 'green'),
    ('red', 'blue'),
    ('green', 'red'),
    ('green', 'blue'),
    ('green', 'green'),
    ('blue', 'red'),
    ('blue', 'green'),
    ('blue', 'blue');
SELECT
    id,
    colour_1,
    colour_2
FROM
    my_table;

Output: If everything is as intended, the output will be like as shown below: 
 

Since, our database is good to go, we move onto the implementation of the SELECT DISTINCT clause. 

Retrieve unique values from the 'colour_1' column.

Query:

SELECT
    DISTINCT colour_1
FROM
    my_table
ORDER BY
    colour_1;

Output:  

Retrieve unique combinations of values from the 'colour_1' and 'colour_2' columns.

Query:

SELECT
    DISTINCT colour_1,
    colour_2
FROM
    my_table
ORDER BY
    colour_1,
    colour_2;

Output: 

• The DISTINCT clause compares entire rows, not just individual columns. This means that if you use SELECT DISTINCT *, PostgreSQL will return unique rows based on all column values. 
• The DISTINCT clause treats NULL values as equal. Therefore, rows with NULL values in the specified columns are considered duplicates.
• The order of columns in the SELECT DISTINCT clause matters. Changing the order of columns can yield different results if the dataset has duplicates across multiple columns.
• DISTINCT can be used within subqueries to filter out duplicates before applying additional operations.

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