O'Reilly logo

PostgreSQL 9 Administration Cookbook by Hannu Krosing, Simon Riggs

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required