PostgreSQL: How to SELECT DISTINCT on Multiple Columns

Often in PostgreSQL you may want to use SELECT DISTINCT on multiple columns.

You can use the following basic syntax to do so:

SELECT team, position
FROM athletes
GROUP BY team, position;

This particular example will return only the distinct combinations of values from the team and position columns in the athletes table.

The following example shows how to use this syntax in practice.

Example: How to SELECT DISTINCT on Multiple Columns in PostgreSQL

Suppose that we create the following table named athletes that contains information about various basketball players:

-- create table named 'athletes'
CREATE TABLE athletes (
  athleteID INTEGER PRIMARY KEY,
  team TEXT,
  position TEXT,
  points INTEGER
);

-- add rows to the table 
INSERT INTO athletes VALUES (0001, 'Mavs', 'Guard', 15);
INSERT INTO athletes VALUES (0002, 'Mavs', 'Guard', 12);
INSERT INTO athletes VALUES (0003, 'Mavs', 'Forward', 23);
INSERT INTO athletes VALUES (0004, 'Hawks', 'Guard', 29);
INSERT INTO athletes VALUES (0005, 'Hawks', 'Forward', 14);
INSERT INTO athletes VALUES (0006, 'Hawks', 'Forward', 33);

-- view all rows in table
SELECT * FROM athletes;

 athleteid | team  | position | points 
-----------+-------+----------+--------
         1 | Mavs  | Guard    |     15
         2 | Mavs  | Guard    |     12
         3 | Mavs  | Forward  |     23
         4 | Hawks | Guard    |     29
         5 | Hawks | Forward  |     14
         6 | Hawks | Forward  |     33

Suppose that we would like to select only the distinct values across the team and position columns of the table.

If we use the SELECT statement without the GROUP BY statement, then it’s possible for duplicate combinations of values to be returned:

-- select all values from team and position columns
SELECT team, position
FROM athletes

 team  | position 
-------+----------
 Mavs  | Guard
 Mavs  | Guard
 Mavs  | Forward
 Hawks | Guard
 Hawks | Forward
 Hawks | Forward

However, if we include the GROUP BY statement then we will return only the distinct combinations of values from the team and position columns:

-- select only distinct values from team and position columns
SELECT team, position
FROM athletes
GROUP BY team, position;

 team  | position 
-------+----------
 Hawks | Guard
 Mavs  | Forward
 Mavs  | Guard
 Hawks | Forward

The resulting table shows only the distinct combinations of values from the team and position columns in the table.

Note: In this example we selected distinct values from two specific columns in a table but you can include as many column names as you’d like to select distinct values from even more columns.

Additional Resources

The following tutorials explain how to perform other common tasks in PostgreSQL:

PostgreSQL: How to Select the Last Row per Group
PostgreSQL: How to INSERT If Not Exists Already
PostgreSQL: How to Calculate Difference Between Rows