Prefix, a PostgreSQL GiST opclass

The prefix project is a PostgreSQL module allowing the RDBMs to index text fields by them prefix, and to use this index (if available and when the planner thinks it's worth it) for prefix matching queries.

An example? -ok:

create table prefixes (
       prefix    text primary key,
       name      text not null,
       shortname text,
       state     char default 'S',
       check( state in ('S', 'R') )
);
comment on column prefixes.state is 'S:   - R: reserved';

\copy prefixes from 'prefixes.fr.csv' with delimiter ; csv quote '"'

SELECT *
 FROM prefixes
WHERE prefix @> '0123456789'

Here the table prefixes contains a prefix text column in which we'll store a given telephony operator prefix and some details about the operator, such as found on the ART website (French telecom regulation authority).

This article will be expanded later on and provide some details about the prefix usage, and provided I find some time for this I'll try to write up a simple GiST tutorial explaining how to create a new GiST indexing method for PostgreSQL.