1ALTER OPERATOR FAMILY(7) PostgreSQL 15.4 DocumentationALTER OPERATOR FAMILY(7)
2
6 ALTER_OPERATOR_FAMILY - change the definition of an operator family
7
9 ALTER OPERATOR FAMILY name USING index_method ADD
10 { OPERATOR strategy_number operator_name ( op_type, op_type )
11 [ FOR SEARCH | FOR ORDER BY sort_family_name ]
12 | FUNCTION support_number [ ( op_type [ , op_type ] ) ]
13 function_name [ ( argument_type [, ...] ) ]
14 } [, ... ]
15 ALTER OPERATOR FAMILY name USING index_method DROP
17 { OPERATOR strategy_number ( op_type [ , op_type ] )
18 | FUNCTION support_number ( op_type [ , op_type ] )
19 } [, ... ]
20 ALTER OPERATOR FAMILY name USING index_method
22 RENAME TO new_name
23 ALTER OPERATOR FAMILY name USING index_method
25 OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
26 ALTER OPERATOR FAMILY name USING index_method
28 SET SCHEMA new_schema
29
31 ALTER OPERATOR FAMILY changes the definition of an operator family. You
32 can add operators and support functions to the family, remove them from
33 the family, or change the family's name or owner.
34 When operators and support functions are added to a family with ALTER
36 OPERATOR FAMILY, they are not part of any specific operator class
37 within the family, but are just “loose” within the family. This
38 indicates that these operators and functions are compatible with the
39 family's semantics, but are not required for correct functioning of any
40 specific index. (Operators and functions that are so required should be
41 declared as part of an operator class, instead; see CREATE OPERATOR
42 CLASS (CREATE_OPERATOR_CLASS(7)).) PostgreSQL will allow loose members
43 of a family to be dropped from the family at any time, but members of
44 an operator class cannot be dropped without dropping the whole class
45 and any indexes that depend on it. Typically, single-data-type
46 operators and functions are part of operator classes because they are
47 needed to support an index on that specific data type, while
48 cross-data-type operators and functions are made loose members of the
49 family.
50 You must be a superuser to use ALTER OPERATOR FAMILY. (This restriction
52 is made because an erroneous operator family definition could confuse
53 or even crash the server.)
54 ALTER OPERATOR FAMILY does not presently check whether the operator
56 family definition includes all the operators and functions required by
57 the index method, nor whether the operators and functions form a
58 self-consistent set. It is the user's responsibility to define a valid
59 operator family.
60 Refer to Section 38.16 for further information.
62
64 name
65 The name (optionally schema-qualified) of an existing operator
66 family.
67 index_method
69 The name of the index method this operator family is for.
70 strategy_number
72 The index method's strategy number for an operator associated with
73 the operator family.
74 operator_name
76 The name (optionally schema-qualified) of an operator associated
77 with the operator family.
78 op_type
80 In an OPERATOR clause, the operand data type(s) of the operator, or
81 NONE to signify a prefix operator. Unlike the comparable syntax in
82 CREATE OPERATOR CLASS, the operand data types must always be
83 specified.
84 In an ADD FUNCTION clause, the operand data type(s) the function is
86 intended to support, if different from the input data type(s) of
87 the function. For B-tree comparison functions and hash functions it
88 is not necessary to specify op_type since the function's input data
89 type(s) are always the correct ones to use. For B-tree sort support
90 functions, B-Tree equal image functions, and all functions in GiST,
91 SP-GiST and GIN operator classes, it is necessary to specify the
92 operand data type(s) the function is to be used with.
93 In a DROP FUNCTION clause, the operand data type(s) the function is
95 intended to support must be specified.
96 sort_family_name
98 The name (optionally schema-qualified) of an existing btree
99 operator family that describes the sort ordering associated with an
100 ordering operator.
101 If neither FOR SEARCH nor FOR ORDER BY is specified, FOR SEARCH is
103 the default.
104 support_number
106 The index method's support function number for a function
107 associated with the operator family.
108 function_name
110 The name (optionally schema-qualified) of a function that is an
111 index method support function for the operator family. If no
112 argument list is specified, the name must be unique in its schema.
113 argument_type
115 The parameter data type(s) of the function.
116 new_name
118 The new name of the operator family.
119 new_owner
121 The new owner of the operator family.
122 new_schema
124 The new schema for the operator family.
125 The OPERATOR and FUNCTION clauses can appear in any order.
127
129 Notice that the DROP syntax only specifies the “slot” in the operator
130 family, by strategy or support number and input data type(s). The name
131 of the operator or function occupying the slot is not mentioned. Also,
132 for DROP FUNCTION the type(s) to specify are the input data type(s) the
133 function is intended to support; for GiST, SP-GiST and GIN indexes this
134 might have nothing to do with the actual input argument types of the
135 function.
136 Because the index machinery does not check access permissions on
138 functions before using them, including a function or operator in an
139 operator family is tantamount to granting public execute permission on
140 it. This is usually not an issue for the sorts of functions that are
141 useful in an operator family.
142 The operators should not be defined by SQL functions. An SQL function
144 is likely to be inlined into the calling query, which will prevent the
145 optimizer from recognizing that the query matches an index.
146 Before PostgreSQL 8.4, the OPERATOR clause could include a RECHECK
148 option. This is no longer supported because whether an index operator
149 is “lossy” is now determined on-the-fly at run time. This allows
150 efficient handling of cases where an operator might or might not be
151 lossy.
152
154 The following example command adds cross-data-type operators and
155 support functions to an operator family that already contains B-tree
156 operator classes for data types int4 and int2.
157 ALTER OPERATOR FAMILY integer_ops USING btree ADD
159 -- int4 vs int2
161 OPERATOR 1 < (int4, int2) ,
162 OPERATOR 2 <= (int4, int2) ,
163 OPERATOR 3 = (int4, int2) ,
164 OPERATOR 4 >= (int4, int2) ,
165 OPERATOR 5 > (int4, int2) ,
166 FUNCTION 1 btint42cmp(int4, int2) ,
167 -- int2 vs int4
169 OPERATOR 1 < (int2, int4) ,
170 OPERATOR 2 <= (int2, int4) ,
171 OPERATOR 3 = (int2, int4) ,
172 OPERATOR 4 >= (int2, int4) ,
173 OPERATOR 5 > (int2, int4) ,
174 FUNCTION 1 btint24cmp(int2, int4) ;
175 To remove these entries again:
177 ALTER OPERATOR FAMILY integer_ops USING btree DROP
179 -- int4 vs int2
181 OPERATOR 1 (int4, int2) ,
182 OPERATOR 2 (int4, int2) ,
183 OPERATOR 3 (int4, int2) ,
184 OPERATOR 4 (int4, int2) ,
185 OPERATOR 5 (int4, int2) ,
186 FUNCTION 1 (int4, int2) ,
187 -- int2 vs int4
189 OPERATOR 1 (int2, int4) ,
190 OPERATOR 2 (int2, int4) ,
191 OPERATOR 3 (int2, int4) ,
192 OPERATOR 4 (int2, int4) ,
193 OPERATOR 5 (int2, int4) ,
194 FUNCTION 1 (int2, int4) ;
195
197 There is no ALTER OPERATOR FAMILY statement in the SQL standard.
198
200 CREATE OPERATOR FAMILY (CREATE_OPERATOR_FAMILY(7)), DROP OPERATOR
201 FAMILY (DROP_OPERATOR_FAMILY(7)), CREATE OPERATOR CLASS
202 (CREATE_OPERATOR_CLASS(7)), ALTER OPERATOR CLASS
203 (ALTER_OPERATOR_CLASS(7)), DROP OPERATOR CLASS (DROP_OPERATOR_CLASS(7))
204PostgreSQL 15.4 2023 ALTER OPERATOR FAMILY(7)