Finding a unique key for a set of data

Sometimes, it can be difficult to find a unique set of key columns that describe the data.

Getting ready

Let's start with a small table, where the answer is fairly obvious:

postgres=# select * from ord;

We assume that the output is as follows:

orderid  | customerid |  amt  
---------+------------+--------
   10677 |          2 |   5.50
    5019 |          3 | 277.44
    9748 |          3 |  77.17
(3 rows)

How to do it…

First of all, there's no need to do this through a brute-force approach. Checking all the permutations of columns to see which is unique might take you a long time.

Let's start by using PostgreSQL's own optimizer statistics. Run the following command on our table to get a fresh sample of statistics:

postgres=# analyze ord;
ANALYZE

This runs quickly, so we don't have to wait too long. Now we can examine the relevant columns of the statistics:

postgres=# SELECT attname, n_distinct 
                           FROM pg_stats
                        ...