I started my programming career as an Oracle DBA. It took a few years but eventually I got fed up with the corporate world and I went about doing my own thing.

When I no longer had the comfy cushion of Oracle enterprise edition I discovered PostgreSQL. After I gotten over not having proper partitions and MERGE statement (aka UPSERT) I found some nice unique features in PostgreSQL. Oddly enough, a lot of them contained the word DISTINCT.

Duplication is scary (The shining, 1980)

DISTINCT

I created a simple Employee table with name, department and salary using mock data from this site:

haki=# \d employee

Table "public.employee"

Column | Type | Modifiers

------------+-----------------------+-----------

id | integer | not null

name | character varying(30) |

department | character varying(30) |

salary | integer |

Indexes:

"employee_pkey" PRIMARY KEY, btree (id)

haki=# select * from employee limit 5;

id | name | department | salary

----+----------------+----------------------+--------

1 | Carl Frazier | Engineering | 3052

2 | Richard Fox | Product Management | 13449

3 | Carolyn Carter | Engineering | 8366

4 | Benjamin Brown | Business Development | 7386

5 | Diana Fisher | Services | 10419

(5 rows)

What is DISTINCT?

SELECT DISTINCT eliminates duplicate rows from the result.

The simplest use of distinct is, for example, to get a unique list of departments:

haki=# SELECT DISTINCT department FROM employee;

department

--------------------------

Services

Support

Training

Accounting

Business Development

Marketing

Product Management

Human Resources

Engineering

Sales

Research and Development

Legal

(12 rows)

(easy CS students, I know it’s not normalized…)

We can do the same thing with group by

SELECT department FROM employee GROUP BY department;

but we are talking about DISTINCT.

DISTINCT ON

A classic job interview question is finding the employee with the highest salary in each department.

This is what they teach in the university:

SELECT

*

FROM

employee

WHERE

(department, salary) IN (

SELECT

department,

MAX(salary)

FROM

employee

GROUP BY

department

)

ORDER BY

department; id | name | department | salary

----+------------------+--------------------------+--------

30 | Sara Roberts | Accounting | 13845

4 | Benjamin Brown | Business Development | 7386

3 | Carolyn Carter | Engineering | 8366

20 | Janet Hall | Human Resources | 2826

14 | Chris Phillips | Legal | 3706

10 | James Cunningham | Legal | 3706

11 | Richard Bradley | Marketing | 11272

2 | Richard Fox | Product Management | 13449

25 | Evelyn Rodriguez | Research and Development | 10628

17 | Benjamin Carter | Sales | 6197

24 | Jessica Elliott | Services | 14542

7 | Bonnie Robertson | Support | 12674

8 | Jean Bailey | Training | 13230

Legal has two employees with the same high salary. Depending on the use case, this query can get pretty nasty.

If you graduated a while back, you already know a few things about databases and you heard about analytic and window functions, you might do this:

WITH ranked_employees AS (

SELECT

ROW_NUMBER() OVER (

PARTITION BY department ORDER BY salary DESC

) AS rn,

*

FROM

employee

)

SELECT

*

FROM

ranked_employees

WHERE

rn = 1

ORDER BY

department;

The result is the same without the duplicates:

rn | id | name | department | salary

----+----+------------------+--------------------------+--------

1 | 30 | Sara Roberts | Accounting | 13845

1 | 4 | Benjamin Brown | Business Development | 7386

1 | 3 | Carolyn Carter | Engineering | 8366

1 | 20 | Janet Hall | Human Resources | 2826

1 | 14 | Chris Phillips | Legal | 3706

1 | 11 | Richard Bradley | Marketing | 11272

…

Up until now, this is what I would have done.

Now for the real treat, PostgreSQL has a special nonstandard clause to find the first row in a group:

SELECT DISTINCT ON (department)

*

FROM

employee

ORDER BY

department,

salary DESC;

This is wild! Why nobody told me this is possible?

The docs explain DISTINCT ON:

SELECT DISTINCT ON ( expression [, …] ) keeps only the first row of each set of rows where the given expressions evaluate to equal

And the reason I haven’t heard about it is:

Nonstandard Clauses

DISTINCT ON ( … ) is an extension of the SQL standard.

PostgreSQL does all the heavy lifting for us. The only requirement is that we ORDER BY the field we group by ( department in this case). It also allows for “grouping” by more than one field which only makes this clause even more powerful.

IS DISTINCT FROM

Comparing values in SQL can result in three outcomes — true , false or unknown :

WITH t AS (

SELECT 1 AS a, 1 AS b UNION ALL

SELECT 1, 2 UNION ALL

SELECT NULL, 1 UNION ALL

SELECT NULL, NULL

)

SELECT

a,

b,

a = b as equal

FROM

t; a | b | equal

------+------+-------

1 | 1 | t

1 | 2 | f

NULL | 1 | NULL

NULL | NULL | NULL

The result of comparing NULL with NULL using equality (=) is UNKNOWN (marked as NULL in the table).

In SQL 1 = 1 and NULL IS NULL but NULL != NULL.

It’s important to be aware of this subtlety because comparing nullable fields might yield unexpected results.

The full condition to get either true or false when comparing nullable fields is:

(a is null and b is null)

or

(a is not null and b is not null and a = b)

And the result:

a | b | equal | full_condition

------+------+-------+----------

1 | 1 | t | t

1 | 2 | f | f

NULL | 1 | NULL | f

NULL | NULL | NULL | t



This is the result we want but it is very long. Is there a better way?

PostgreSQL implements the SQL standard for safely comparing nullable fields:

SELECT

a,

b,

a = b as equal,

a IS DISTINCT FROM b AS is_distinct_from

FROM

t; a | b | equal | is_distinct_from

------+------+-------+------------------

1 | 1 | t | f

1 | 2 | f | t

NULL | 1 | NULL | t

NULL | NULL | NULL | f

PostgreSQL wiki explain IS DISTINCT FROM :

IS DISTINCT FROM and IS NOT DISTINCT FROM … treat NULL as if it was a known value, rather than a special case for unknown.

Much better — short and verbose.

How other databases handle this?

MySQL — a special operator <=> with similar functionality.

a special operator with similar functionality. Oracle — Provide a function called LNNVL to compare nullable fields (good luck with that…).

Provide a function called LNNVL to compare nullable fields (good luck with that…). MSSQL — Couldn’t find a similar function.

ARRAY_AGG (DISTINCT)

ARRAY_AGG was one of the major selling points of PostgreSQL when I was transitioning from Oracle.

ARRAY_AGG aggregates values into an array:

SELECT

department,

ARRAY_AGG(name) AS employees

FROM

employee

GROUP BY

department; department | employees

--------------------------+-------------------------------------

Services | {"Diana Fisher","Jessica Elliott"}

Support | {"Bonnie Robertson"}

Training | {"Jean Bailey"}

Accounting | {"Phillip Reynolds","Sean Franklin"}

Business Development | {"Benjamin Brown","Brian Hayes"}

Marketing | {"Richard Bradley","Arthur Moreno"}

Product Management | {"Richard Fox","Randy Wells"}

Human Resources | {"Janet Hall"}

Engineering | {"Carl Frazier","Carolyn Carter"}

Sales | {"Benjamin Carter"}

Research and Development | {"Donna Reynolds","Ann Boyd"}

Legal | {"James Cunningham","George Hanson"}

I find ARRAY_AGG useful mostly in the CLI for getting a quick view of the data, or when used with an ORM.

PostgreSQL took it the extra mile and implemented the DISTINCT option for this aggregate function as well. Using DISTINCT we can, for example, quickly view the unique salaries in each department:

SELECT

department,

ARRAY_AGG(DISTINCT salary) AS salaries

FROM

employee

GROUP BY

department; department | salaries

--------------------------+---------------

Accounting | {11203}

Business Development | {2196,7386}

Engineering | {1542,3052}

Human Resources | {2826}

Legal | {1079,3706}

Marketing | {5740}

Product Management | {9101,13449}

Research and Development | {6451,10628}

Sales | {6197}

Services | {2119}

Support | {12674}

Training | {13230}

We can immediately see that everyone in the support department are making the same salary.

How other databases handle this?

MySQL — Has a similar function called GROUP_CONCAT.

— Has a similar function called GROUP_CONCAT. Oracle — Has an aggregate function called ListAgg. It has no support for DISTINCT. Oracle introduced the function in version 11.2 and up until then the world wide web was filled with custom implementations.

— Has an aggregate function called ListAgg. It has no support for DISTINCT. Oracle introduced the function in version 11.2 and up until then the world wide web was filled with custom implementations. MsSQL — The closest I found was a function called STUFF that accepts an expression.

Take away

The only take away from this article is that you should always go back to the basics!