EVOLUTION-MANAGER

Edit File: CREATE_OPERATOR_CLASS.7

'\" t .\" Title: CREATE OPERATOR CLASS .\" Author: The PostgreSQL Global Development Group .\" Generator: DocBook XSL Stylesheets vsnapshot <http://docbook.sf.net/> .\" Date: 2025 .\" Manual: PostgreSQL 14.18 Documentation .\" Source: PostgreSQL 14.18 .\" Language: English .\" .TH "CREATE OPERATOR CLASS" "7" "2025" "PostgreSQL 14.18" "PostgreSQL 14.18 Documentation" .\" ----------------------------------------------------------------- .\" * Define some portability stuff .\" ----------------------------------------------------------------- .\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ .\" http://bugs.debian.org/507673 .\" http://lists.gnu.org/archive/html/groff/2009-02/msg00013.html .\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ .ie \n(.g .ds Aq \(aq .el .ds Aq ' .\" ----------------------------------------------------------------- .\" * set default formatting .\" ----------------------------------------------------------------- .\" disable hyphenation .nh .\" disable justification (adjust text to left margin only) .ad l .\" ----------------------------------------------------------------- .\" * MAIN CONTENT STARTS HERE * .\" ----------------------------------------------------------------- .SH "NAME" CREATE_OPERATOR_CLASS \- define a new operator class .SH "SYNOPSIS" .sp .nf CREATE OPERATOR CLASS \fIname\fR [ DEFAULT ] FOR TYPE \fIdata_type\fR USING \fIindex_method\fR [ FAMILY \fIfamily_name\fR ] AS { OPERATOR \fIstrategy_number\fR \fIoperator_name\fR [ ( \fIop_type\fR, \fIop_type\fR ) ] [ FOR SEARCH | FOR ORDER BY \fIsort_family_name\fR ] | FUNCTION \fIsupport_number\fR [ ( \fIop_type\fR [ , \fIop_type\fR ] ) ] \fIfunction_name\fR ( \fIargument_type\fR [, \&.\&.\&.] ) | STORAGE \fIstorage_type\fR } [, \&.\&.\&. ] .fi .SH "DESCRIPTION" .PP \fBCREATE OPERATOR CLASS\fR creates a new operator class\&. An operator class defines how a particular data type can be used with an index\&. The operator class specifies that certain operators will fill particular roles or \(lqstrategies\(rq for this data type and this index method\&. The operator class also specifies the support functions to be used by the index method when the operator class is selected for an index column\&. All the operators and functions used by an operator class must be defined before the operator class can be created\&. .PP If a schema name is given then the operator class is created in the specified schema\&. Otherwise it is created in the current schema\&. Two operator classes in the same schema can have the same name only if they are for different index methods\&. .PP The user who defines an operator class becomes its owner\&. Presently, the creating user must be a superuser\&. (This restriction is made because an erroneous operator class definition could confuse or even crash the server\&.) .PP \fBCREATE OPERATOR CLASS\fR does not presently check whether the operator class definition includes all the operators and functions required by the index method, nor whether the operators and functions form a self\-consistent set\&. It is the user\*(Aqs responsibility to define a valid operator class\&. .PP Related operator classes can be grouped into operator families\&. To add a new operator class to an existing family, specify the FAMILY option in \fBCREATE OPERATOR CLASS\fR\&. Without this option, the new class is placed into a family named the same as the new class (creating that family if it doesn\*(Aqt already exist)\&. .PP Refer to Section\ \&38.16 for further information\&. .SH "PARAMETERS" .PP \fIname\fR .RS 4 The name of the operator class to be created\&. The name can be schema\-qualified\&. .RE .PP DEFAULT .RS 4 If present, the operator class will become the default operator class for its data type\&. At most one operator class can be the default for a specific data type and index method\&. .RE .PP \fIdata_type\fR .RS 4 The column data type that this operator class is for\&. .RE .PP \fIindex_method\fR .RS 4 The name of the index method this operator class is for\&. .RE .PP \fIfamily_name\fR .RS 4 The name of the existing operator family to add this operator class to\&. If not specified, a family named the same as the operator class is used (creating it, if it doesn\*(Aqt already exist)\&. .RE .PP \fIstrategy_number\fR .RS 4 The index method\*(Aqs strategy number for an operator associated with the operator class\&. .RE .PP \fIoperator_name\fR .RS 4 The name (optionally schema\-qualified) of an operator associated with the operator class\&. .RE .PP \fIop_type\fR .RS 4 In an OPERATOR clause, the operand data type(s) of the operator, or NONE to signify a prefix operator\&. The operand data types can be omitted in the normal case where they are the same as the operator class\*(Aqs data type\&. .sp In a FUNCTION clause, the operand data type(s) the function is intended to support, if different from the input data type(s) of the function (for B\-tree comparison functions and hash functions) or the class\*(Aqs data type (for B\-tree sort support functions, B\-tree equal image functions, and all functions in GiST, SP\-GiST, GIN and BRIN operator classes)\&. These defaults are correct, and so \fIop_type\fR need not be specified in FUNCTION clauses, except for the case of a B\-tree sort support function that is meant to support cross\-data\-type comparisons\&. .RE .PP \fIsort_family_name\fR .RS 4 The name (optionally schema\-qualified) of an existing btree operator family that describes the sort ordering associated with an ordering operator\&. .sp If neither FOR SEARCH nor FOR ORDER BY is specified, FOR SEARCH is the default\&. .RE .PP \fIsupport_number\fR .RS 4 The index method\*(Aqs support function number for a function associated with the operator class\&. .RE .PP \fIfunction_name\fR .RS 4 The name (optionally schema\-qualified) of a function that is an index method support function for the operator class\&. .RE .PP \fIargument_type\fR .RS 4 The parameter data type(s) of the function\&. .RE .PP \fIstorage_type\fR .RS 4 The data type actually stored in the index\&. Normally this is the same as the column data type, but some index methods (currently GiST, GIN, SP\-GiST and BRIN) allow it to be different\&. The STORAGE clause must be omitted unless the index method allows a different type to be used\&. If the column \fIdata_type\fR is specified as anyarray, the \fIstorage_type\fR can be declared as anyelement to indicate that the index entries are members of the element type belonging to the actual array type that each particular index is created for\&. .RE .PP The OPERATOR, FUNCTION, and STORAGE clauses can appear in any order\&. .SH "NOTES" .PP Because the index machinery does not check access permissions on functions before using them, including a function or operator in an operator class is tantamount to granting public execute permission on it\&. This is usually not an issue for the sorts of functions that are useful in an operator class\&. .PP The operators should not be defined by SQL functions\&. An SQL function is likely to be inlined into the calling query, which will prevent the optimizer from recognizing that the query matches an index\&. .PP Before PostgreSQL 8\&.4, the OPERATOR clause could include a RECHECK option\&. This is no longer supported because whether an index operator is \(lqlossy\(rq is now determined on\-the\-fly at run time\&. This allows efficient handling of cases where an operator might or might not be lossy\&. .SH "EXAMPLES" .PP The following example command defines a GiST index operator class for the data type _int4 (array of int4)\&. See the intarray module for the complete example\&. .sp .if n \{\ .RS 4 .\} .nf CREATE OPERATOR CLASS gist__int_ops DEFAULT FOR TYPE _int4 USING gist AS OPERATOR 3 &&, OPERATOR 6 = (anyarray, anyarray), OPERATOR 7 @>, OPERATOR 8 <@, OPERATOR 20 @@ (_int4, query_int), FUNCTION 1 g_int_consistent (internal, _int4, smallint, oid, internal), FUNCTION 2 g_int_union (internal, internal), FUNCTION 3 g_int_compress (internal), FUNCTION 4 g_int_decompress (internal), FUNCTION 5 g_int_penalty (internal, internal, internal), FUNCTION 6 g_int_picksplit (internal, internal), FUNCTION 7 g_int_same (_int4, _int4, internal); .fi .if n \{\ .RE .\} .SH "COMPATIBILITY" .PP \fBCREATE OPERATOR CLASS\fR is a PostgreSQL extension\&. There is no \fBCREATE OPERATOR CLASS\fR statement in the SQL standard\&. .SH "SEE ALSO" ALTER OPERATOR CLASS (\fBALTER_OPERATOR_CLASS\fR(7)), DROP OPERATOR CLASS (\fBDROP_OPERATOR_CLASS\fR(7)), CREATE OPERATOR FAMILY (\fBCREATE_OPERATOR_FAMILY\fR(7)), ALTER OPERATOR FAMILY (\fBALTER_OPERATOR_FAMILY\fR(7))