In PostgreSQL, the SELECT DISTINCT statement is used to fetch only unique values from a table/result-set that is returned by a query. The DISTINCT clause retains only one row from a set of duplicated rows. You can use the DISTINCT clause on multiple columns as well. In such a case, the DISTINCT clause will determine the duplicates based on the combination of the targeted columns' values.

This write-up will show you how to fetch the distinct values from a result set returned by a query. So, let’s begin.

How to Fetch Unique Records in PostgreSQL?

Follow the below syntax to fetch the distinct values from a result set:

SELECT DISTINCT col_list
FROM tab_name;

Firstly, specify a column name or list of columns after the SELECT DISTINCT clause. After that, write the targeted table's name in the FROM clause.

Example #1: How to Fetch Unique Rows From a Table in PostgreSQL?

In this example, we will target the programming_languages table, whose details are as follows:

SELECT * FROM programming_languages;

From the output, you can notice that there are multiple duplicates in the programming_languages table. Let’s run the SELECT command with the DISTINCT clause to fetch only the DISTINCT rows from the selected table:

SELECT DISTINCT language
FROM programming_languages;

This way, you can fetch the unique rows from a table in PostgreSQL.

Example #2: How to Fetch Unique Values From Multiple Columns in PostgreSQL?

If you apply the DISTINCT clause on multiple columns, then it will skip only those values that are duplicated in all the columns:

SELECT DISTINCT id, language
FROM programming_languages;

There was only one duplicate record that contained the same id and language, i.e., id=2, language=C++, so, the SELECT DISTINCT clause removed only that record. Other than that, the SELECT DISTINCT clause fetched all the values, including duplicates. This is because the id column has only one duplicate value, i.e., 2.

Example #3: What Does DISTINCT ON Clause do in PostgreSQL?

In the case of multiple columns, use the Postgres DISTINCT ON clause to get the first row from the set of duplicate rows, regardless of the combination of the unique column values. However, you must specify the targeted column in the ORDER BY clause; otherwise, an error will occur.

SELECT DISTINCT ON (language) id, language
FROM programming_languages
ORDER BY language;

This way, you can fetch more than one column and get the unique values based on a specific column.

Conclusion

PostgreSQL uses the SELECT DISTINCT clause to return only unique values from a query’s result set. It retains only one row from a set of duplicated rows. The SELECT DISTINCT clause can be used on multiple columns to fetch the duplicates from multiple columns. However, in such a case, the SELECT DISTINCT clause will skip only those duplicates that are duplicated in all the columns. This write-up explained the working of the SELECT DISTINCT clause with practical examples.