PostgreSQL 11 Administration Cookbook

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                           WHERE schemaname = 'public'                            AND tablename = 'ord';  attname   | n_distinct------------+------------ orderid    |         -1 customerid |  -0.666667 amt        |         -1(3 rows)

The preceding example was chosen because we have two potential answers. If the value ...