1ALTER OPERATOR FAMILY(7) PostgreSQL 11.3 DocumentationALTER OPERATOR FAMILY(7)
2
3
4

NAME

6       ALTER_OPERATOR_FAMILY - change the definition of an operator family
7

SYNOPSIS

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
16       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
21       ALTER OPERATOR FAMILY name USING index_method
22           RENAME TO new_name
23
24       ALTER OPERATOR FAMILY name USING index_method
25           OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
26
27       ALTER OPERATOR FAMILY name USING index_method
28           SET SCHEMA new_schema
29

DESCRIPTION

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
35       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
51       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
55       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
61       Refer to Section 38.15 for further information.
62

PARAMETERS

64       name
65           The name (optionally schema-qualified) of an existing operator
66           family.
67
68       index_method
69           The name of the index method this operator family is for.
70
71       strategy_number
72           The index method's strategy number for an operator associated with
73           the operator family.
74
75       operator_name
76           The name (optionally schema-qualified) of an operator associated
77           with the operator family.
78
79       op_type
80           In an OPERATOR clause, the operand data type(s) of the operator, or
81           NONE to signify a left-unary or right-unary operator. Unlike the
82           comparable syntax in CREATE OPERATOR CLASS, the operand data types
83           must always be specified.
84
85           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 and all functions in GiST, SP-GiST and GIN operator
91           classes, it is necessary to specify the operand data type(s) the
92           function is to be used with.
93
94           In a DROP FUNCTION clause, the operand data type(s) the function is
95           intended to support must be specified.
96
97       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
102           If neither FOR SEARCH nor FOR ORDER BY is specified, FOR SEARCH is
103           the default.
104
105       support_number
106           The index method's support function number for a function
107           associated with the operator family.
108
109       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
114       argument_type
115           The parameter data type(s) of the function.
116
117       new_name
118           The new name of the operator family.
119
120       new_owner
121           The new owner of the operator family.
122
123       new_schema
124           The new schema for the operator family.
125
126       The OPERATOR and FUNCTION clauses can appear in any order.
127

NOTES

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
137       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
143       The operators should not be defined by SQL functions. A SQL function is
144       likely to be inlined into the calling query, which will prevent the
145       optimizer from recognizing that the query matches an index.
146
147       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

EXAMPLES

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
158           ALTER OPERATOR FAMILY integer_ops USING btree ADD
159
160             -- 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
168             -- 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
176       To remove these entries again:
177
178           ALTER OPERATOR FAMILY integer_ops USING btree DROP
179
180             -- 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
188             -- 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

COMPATIBILITY

197       There is no ALTER OPERATOR FAMILY statement in the SQL standard.
198

SEE ALSO

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))
204
205
206
207PostgreSQL 11.3                      2019             ALTER OPERATOR FAMILY(7)
Impressum