Database Reference
In-Depth Information
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;
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 of 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 not long to wait.
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 of
n_distinct is -1, then the column is thought to be unique within the sample of rows examined.
