Introduction to PostgreSQL SELECT DISTINCT clause

TheSELECT DISTINCT removes duplicate rows from a result set. TheSELECT DISTINCT clause retains one row for each group of duplicates.

TheSELECT DISTINCT clause can be applied to one or more columns in the select list of theSELECT statement.

The following illustrates the syntax of the DISTINCT clause:

SELECT  DISTINCT column1FROM  table_name;

In this syntax, theSELECT DISTINCT uses the values in thecolumn1 column to evaluate the duplicate.

If you specify multiple columns, theSELECT DISTINCT clause will evaluate the duplicate based on the combination of values in these columns. For example:

SELECT   DISTINCT column1, column2FROM   table_name;

In this syntax, theSELECT DISTINCT uses the combination of values in bothcolumn1 andcolumn2 columns for evaluating the duplicate.

Note that PostgreSQL also offers theDISTINCT ON clause that retains the first unique entry of a column or combination of columns in the result set.

If you want to find distinct values of all columns in a table, you can useSELECT DISTINCT *:

SELECT DISTINCT *FROM table_name;

The star or asterisk (*) means all columns of thetable_name.

PostgreSQL SELECT DISTINCT examples

Let’screate a new table to practice the SELECT DISTINCT clause.

Note that you will learn how tocreate a table andinsert data into it in the subsequent tutorial. In this tutorial, you need to execute the statement inpsql or use pgAdmin to execute the statements.

First, create the colors table that has three columns:id,bcolor andfcolor using the followingCREATE TABLE statement:

CREATE TABLE colors(  id SERIAL PRIMARY KEY,  bcolor VARCHAR,  fcolor VARCHAR);

Second,insert some rows into thecolors table:

INSERT INTO  colors (bcolor, fcolor)VALUES  ('red','red'),  ('red','red'),  ('red',NULL),  (NULL,'red'),  (NULL,NULL),  ('green','green'),  ('blue','blue'),  ('blue','blue');

Third, retrieve the data from thecolors table using theSELECT statement:

SELECT  id,  bcolor,  fcolorFROM  colors;

Output:

id | bcolor | fcolor----+--------+--------  1 | red    | red  2 | red    | red  3 | red    | null  4 | null   | red  5 | null   | null  6 | green  | green  7 | blue   | blue  8 | blue   | blue(8 rows)

1) PostgreSQL SELECT DISTINCT one column example

The following statement selects unique values from thebcolor column of thecolors table andsorts the result set in alphabetical order by using theORDER BY clause.

SELECT  DISTINCT bcolorFROM  colorsORDER BY  bcolor;

Output:

bcolor-------- blue green red null(4 rows)

Thebcolor column has three 'red' entries, twoNULL, one 'green', and two 'blue'.SELECT DISTINCT removes two 'red' values, oneNULL, and one 'blue'.

Note that PostgreSQL treatsNULLs as duplicates so that it keeps oneNULL for allNULLs when you apply theSELECT DISTINCT clause.

2) SELECT DISTINCT on multiple columns

The following statement applies theSELECT DISTINCT clause to bothbcolor andfcolor columns:

SELECT  DISTINCT bcolor, fcolorFROM  colorsORDER BY  bcolor,  fcolor;

Output:

bcolor | fcolor--------+-------- blue   | blue green  | green red    | red red    | null null   | red null   | null(6 rows)

In this example, the query uses the values from bothbcolor andfcolor columns to evaluate the uniqueness of rows.

3) Using the SELECT DISTINCT clause in practice

In practice, you often use theSELECT DISTINCT clause to analyze the uniqueness of values in a column.

For example, you may want to know how many rental rates for films from thefilm table:

To achieve this, you can specify therental_rate column in theSELECT DISTINCT clause as follows:

SELECT DISTINCT  rental_rateFROM  filmORDER BY  rental_rate;

Output:

rental_rate-------------        0.99        2.99        4.99(3 rows)

The output indicates that there are only three distinct rental rates 0.99, 2.99, and 4.99.

Note that for executing the query above your current database should bedvdrental. To switch the current database todvdrental run:

\c dvdrental

Summary

• Use theSELECT DISTINCT to remove duplicate rows from a result set of a query.

Last updated onApril 19, 2024

On this page

• Introduction to PostgreSQL SELECT DISTINCT clause
• PostgreSQL SELECT DISTINCT examples
• Summary