PostgreSQL - SELECT DISTINCT clause

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.

Syntax

For a single column:

SELECT DISTINCT column_1 FROM table_name;

For multiple columns:

SELECT DISTINCT column_1, column_2, column_3 FROM table_name;

PostgreSQL SELECT DISTINCT clause Examples

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. 

Example 1: PostgreSQL DISTINCT on One Column

Retrieve unique values from the 'colour_1' column.

Query:

SELECT
    DISTINCT colour_1
FROM
    my_table
ORDER BY
    colour_1;

Output:  

Example 2: PostgreSQL DISTINCT on multiple columns 

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: 

Important Points About PostgreSQL SELECT DISTINCT clause

• 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.