CREATE_OPERATOR(7)

1CREATE OPERATOR(7)       PostgreSQL 15.4 Documentation      CREATE OPERATOR(7)
2
3
4

NAME

6       CREATE_OPERATOR - define a new operator
7

SYNOPSIS

9       CREATE OPERATOR name (
10           {FUNCTION|PROCEDURE} = function_name
11           [, LEFTARG = left_type ] [, RIGHTARG = right_type ]
12           [, COMMUTATOR = com_op ] [, NEGATOR = neg_op ]
13           [, RESTRICT = res_proc ] [, JOIN = join_proc ]
14           [, HASHES ] [, MERGES ]
15       )
16

DESCRIPTION

18       CREATE OPERATOR defines a new operator, name. The user who defines an
19       operator becomes its owner. If a schema name is given then the operator
20       is created in the specified schema. Otherwise it is created in the
21       current schema.
22
23       The operator name is a sequence of up to NAMEDATALEN-1 (63 by default)
24       characters from the following list:
25
26           + - * / < > = ~ ! @ # % ^ & | ` ?
27
28       There are a few restrictions on your choice of name:
29
30       •   -- and /* cannot appear anywhere in an operator name, since they
31           will be taken as the start of a comment.
32
33       •   A multicharacter operator name cannot end in + or -, unless the
34           name also contains at least one of these characters:
35
36               ~ ! @ # % ^ & | ` ?
37
38           For example, @- is an allowed operator name, but *- is not. This
39           restriction allows PostgreSQL to parse SQL-compliant commands
40           without requiring spaces between tokens.
41
42       •   The use of => as an operator name is deprecated. It may be
43           disallowed altogether in a future release.
44
45       The operator != is mapped to <> on input, so these two names are always
46       equivalent.
47
48       For binary operators, both LEFTARG and RIGHTARG must be defined. For
49       prefix operators only RIGHTARG should be defined. The function_name
50       function must have been previously defined using CREATE FUNCTION and
51       must be defined to accept the correct number of arguments (either one
52       or two) of the indicated types.
53
54       In the syntax of CREATE OPERATOR, the keywords FUNCTION and PROCEDURE
55       are equivalent, but the referenced function must in any case be a
56       function, not a procedure. The use of the keyword PROCEDURE here is
57       historical and deprecated.
58
59       The other clauses specify optional operator optimization clauses. Their
60       meaning is detailed in Section 38.15.
61
62       To be able to create an operator, you must have USAGE privilege on the
63       argument types and the return type, as well as EXECUTE privilege on the
64       underlying function. If a commutator or negator operator is specified,
65       you must own these operators.
66

PARAMETERS

68       name
69           The name of the operator to be defined. See above for allowable
70           characters. The name can be schema-qualified, for example CREATE
71           OPERATOR myschema.+ (...). If not, then the operator is created in
72           the current schema. Two operators in the same schema can have the
73           same name if they operate on different data types. This is called
74           overloading.
75
76       function_name
77           The function used to implement this operator.
78
79       left_type
80           The data type of the operator's left operand, if any. This option
81           would be omitted for a prefix operator.
82
83       right_type
84           The data type of the operator's right operand.
85
86       com_op
87           The commutator of this operator.
88
89       neg_op
90           The negator of this operator.
91
92       res_proc
93           The restriction selectivity estimator function for this operator.
94
95       join_proc
96           The join selectivity estimator function for this operator.
97
98       HASHES
99           Indicates this operator can support a hash join.
100
101       MERGES
102           Indicates this operator can support a merge join.
103
104       To give a schema-qualified operator name in com_op or the other
105       optional arguments, use the OPERATOR() syntax, for example:
106
107           COMMUTATOR = OPERATOR(myschema.===) ,
108

NOTES

110       Refer to Section 38.14 for further information.
111
112       It is not possible to specify an operator's lexical precedence in
113       CREATE OPERATOR, because the parser's precedence behavior is
114       hard-wired. See Section 4.1.6 for precedence details.
115
116       The obsolete options SORT1, SORT2, LTCMP, and GTCMP were formerly used
117       to specify the names of sort operators associated with a merge-joinable
118       operator. This is no longer necessary, since information about
119       associated operators is found by looking at B-tree operator families
120       instead. If one of these options is given, it is ignored except for
121       implicitly setting MERGES true.
122
123       Use DROP OPERATOR to delete user-defined operators from a database. Use
124       ALTER OPERATOR to modify operators in a database.
125

EXAMPLES

127       The following command defines a new operator, area-equality, for the
128       data type box:
129
130           CREATE OPERATOR === (
131               LEFTARG = box,
132               RIGHTARG = box,
133               FUNCTION = area_equal_function,
134               COMMUTATOR = ===,
135               NEGATOR = !==,
136               RESTRICT = area_restriction_function,
137               JOIN = area_join_function,
138               HASHES, MERGES
139           );
140

COMPATIBILITY

142       CREATE OPERATOR is a PostgreSQL extension. There are no provisions for
143       user-defined operators in the SQL standard.
144