PostgreSQL Server Programming - Second Edition

Creating index access methods

So far in this book, you came across examples of creating new data types or user-defined types and operators. What we haven't discussed so far is how to index these types. In PostgreSQL, an index is more of a framework that can be extended or customized for using different strategies. In order to create new index access methods, we have to create an operator class. Let's take a look at a simple example.

Let's consider a scenario where you have to store some special data such as an ID or a social security number in the database. The number may contain non-numeric characters, so it is defined as a text type:

CREATE TABLE test_ssn (ssn text); INSERT INTO test_ssn VALUES ('222-11-020878'); INSERT INTO test_ssn VALUES ('111-11-020978'); ...