- publishing free software manuals
PostgreSQL Reference Manual - Volume 2 - Programming Guide
by The PostgreSQL Global Development Group
Paperback (6"x9"), 408 pages
ISBN 0954612035
RRP £19.95 ($34.95)

Sales of this book support the PostgreSQL project! Get a printed copy>>>

5.14.5 Cross-Data-Type Operator Classes

So far we have implicitly assumed that an operator class deals with only one data type. While there certainly can be only one data type in a particular index column, it is often useful to index operations that compare an indexed column to a value of a different data type. This is presently supported by the B-tree and GiST index methods.

B-trees require the left-hand operand of each operator to be the indexed data type, but the right-hand operand can be of a different type. There must be a support function having a matching signature. For example, the built-in operator class for type bigint (int8) allows cross-type comparisons to int4 and int2. It could be duplicated by this definition:

CREATE OPERATOR CLASS int8_ops
DEFAULT FOR TYPE int8 USING btree AS
  -- standard int8 comparisons
  OPERATOR 1 < ,
  OPERATOR 2 <= ,
  OPERATOR 3 = ,
  OPERATOR 4 >= ,
  OPERATOR 5 > ,
  FUNCTION 1 btint8cmp(int8, int8) ,

  -- cross-type comparisons to int2 (smallint)
  OPERATOR 1 < (int8, int2) ,
  OPERATOR 2 <= (int8, int2) ,
  OPERATOR 3 = (int8, int2) ,
  OPERATOR 4 >= (int8, int2) ,
  OPERATOR 5 > (int8, int2) ,
  FUNCTION 1 btint82cmp(int8, int2) ,

  -- cross-type comparisons to int4 (integer)
  OPERATOR 1 < (int8, int4) ,
  OPERATOR 2 <= (int8, int4) ,
  OPERATOR 3 = (int8, int4) ,
  OPERATOR 4 >= (int8, int4) ,
  OPERATOR 5 > (int8, int4) ,
  FUNCTION 1 btint84cmp(int8, int4) ;

Notice that this definition “overloads” the operator strategy and support function numbers. This is allowed (for B-tree operator classes only) so long as each instance of a particular number has a different right-hand data type. The instances that are not cross-type are the default or primary operators of the operator class.

GiST indexes do not allow overloading of strategy or support function numbers, but it is still possible to get the effect of supporting multiple right-hand data types, by assigning a distinct strategy number to each operator that needs to be supported. The consistent support function must determine what it needs to do based on the strategy number, and must be prepared to accept comparison values of the appropriate data types.

ISBN 0954612035PostgreSQL Reference Manual - Volume 2 - Programming GuideSee the print edition