<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>CREATE OPERATOR</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="sql-creatematerializedview.html" title="CREATE MATERIALIZED VIEW" /><link rel="next" href="sql-createopclass.html" title="CREATE OPERATOR CLASS" /></head><body><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">CREATE OPERATOR</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="sql-creatematerializedview.html" title="CREATE MATERIALIZED VIEW">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><th width="60%" align="center">SQL Commands</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 11.5 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="sql-createopclass.html" title="CREATE OPERATOR CLASS">Next</a></td></tr></table><hr></hr></div><div class="refentry" id="SQL-CREATEOPERATOR"><div class="titlepage"></div><a id="id-1.9.3.72.1" class="indexterm"></a><div class="refnamediv"><h2><span class="refentrytitle">CREATE OPERATOR</span></h2><p>CREATE OPERATOR — define a new operator</p></div><div class="refsynopsisdiv"><h2>Synopsis</h2><pre class="synopsis">
CREATE OPERATOR <em class="replaceable"><code>name</code></em> (
{FUNCTION|PROCEDURE} = <em class="replaceable"><code>function_name</code></em>
[, LEFTARG = <em class="replaceable"><code>left_type</code></em> ] [, RIGHTARG = <em class="replaceable"><code>right_type</code></em> ]
[, COMMUTATOR = <em class="replaceable"><code>com_op</code></em> ] [, NEGATOR = <em class="replaceable"><code>neg_op</code></em> ]
[, RESTRICT = <em class="replaceable"><code>res_proc</code></em> ] [, JOIN = <em class="replaceable"><code>join_proc</code></em> ]
[, HASHES ] [, MERGES ]
)
</pre></div><div class="refsect1" id="id-1.9.3.72.5"><h2>Description</h2><p>
<code class="command">CREATE OPERATOR</code> defines a new operator,
<em class="replaceable"><code>name</code></em>. The user who
defines an operator becomes its owner. If a schema name is given
then the operator is created in the specified schema. Otherwise it
is created in the current schema.
</p><p>
The operator name is a sequence of up to <code class="symbol">NAMEDATALEN</code>-1
(63 by default) characters from the following list:
</p><div class="literallayout"><p><br />
+ - * / < > = ~ ! @ # % ^ & | ` ?<br />
</p></div><p>
There are a few restrictions on your choice of name:
</p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p><code class="literal">--</code> and <code class="literal">/*</code> cannot appear anywhere in an operator name,
since they will be taken as the start of a comment.
</p></li><li class="listitem"><p>
A multicharacter operator name cannot end in <code class="literal">+</code> or
<code class="literal">-</code>,
unless the name also contains at least one of these characters:
</p><div class="literallayout"><p><br />
~ ! @ # % ^ & | ` ?<br />
</p></div><p>
For example, <code class="literal">@-</code> is an allowed operator name,
but <code class="literal">*-</code> is not.
This restriction allows <span class="productname">PostgreSQL</span> to
parse SQL-compliant commands without requiring spaces between tokens.
</p></li><li class="listitem"><p>
The use of <code class="literal">=></code> as an operator name is deprecated. It may
be disallowed altogether in a future release.
</p></li></ul></div><p>
</p><p>
The operator <code class="literal">!=</code> is mapped to
<code class="literal"><></code> on input, so these two names are always
equivalent.
</p><p>
At least one of <code class="literal">LEFTARG</code> and <code class="literal">RIGHTARG</code> must be defined. For
binary operators, both must be defined. For right unary
operators, only <code class="literal">LEFTARG</code> should be defined, while for left
unary operators only <code class="literal">RIGHTARG</code> should be defined.
</p><p>
The <em class="replaceable"><code>function_name</code></em>
function must have been previously defined using <code class="command">CREATE
FUNCTION</code> and must be defined to accept the correct number
of arguments (either one or two) of the indicated types.
</p><p>
In the syntax of <code class="literal">CREATE OPERATOR</code>, the keywords
<code class="literal">FUNCTION</code> and <code class="literal">PROCEDURE</code> are
equivalent, but the referenced function must in any case be a function, not
a procedure. The use of the keyword <code class="literal">PROCEDURE</code> here is
historical and deprecated.
</p><p>
The other clauses specify optional operator optimization clauses.
Their meaning is detailed in <a class="xref" href="xoper-optimization.html" title="38.14. Operator Optimization Information">Section 38.14</a>.
</p><p>
To be able to create an operator, you must have <code class="literal">USAGE</code>
privilege on the argument types and the return type, as well
as <code class="literal">EXECUTE</code> privilege on the underlying function. If a
commutator or negator operator is specified, you must own these operators.
</p></div><div class="refsect1" id="id-1.9.3.72.6"><h2>Parameters</h2><div class="variablelist"><dl class="variablelist"><dt><span class="term"><em class="replaceable"><code>name</code></em></span></dt><dd><p>
The name of the operator to be defined. See above for allowable
characters. The name can be schema-qualified, for example
<code class="literal">CREATE OPERATOR myschema.+ (...)</code>. If not, then
the operator is created in the current schema. Two operators
in the same schema can have the same name if they operate on
different data types. This is called
<em class="firstterm">overloading</em>.
</p></dd><dt><span class="term"><em class="replaceable"><code>function_name</code></em></span></dt><dd><p>
The function used to implement this operator.
</p></dd><dt><span class="term"><em class="replaceable"><code>left_type</code></em></span></dt><dd><p>
The data type of the operator's left operand, if any.
This option would be omitted for a left-unary operator.
</p></dd><dt><span class="term"><em class="replaceable"><code>right_type</code></em></span></dt><dd><p>
The data type of the operator's right operand, if any.
This option would be omitted for a right-unary operator.
</p></dd><dt><span class="term"><em class="replaceable"><code>com_op</code></em></span></dt><dd><p>
The commutator of this operator.
</p></dd><dt><span class="term"><em class="replaceable"><code>neg_op</code></em></span></dt><dd><p>
The negator of this operator.
</p></dd><dt><span class="term"><em class="replaceable"><code>res_proc</code></em></span></dt><dd><p>
The restriction selectivity estimator function for this operator.
</p></dd><dt><span class="term"><em class="replaceable"><code>join_proc</code></em></span></dt><dd><p>
The join selectivity estimator function for this operator.
</p></dd><dt><span class="term"><code class="literal">HASHES</code></span></dt><dd><p>
Indicates this operator can support a hash join.
</p></dd><dt><span class="term"><code class="literal">MERGES</code></span></dt><dd><p>
Indicates this operator can support a merge join.
</p></dd></dl></div><p>
To give a schema-qualified operator name in <em class="replaceable"><code>com_op</code></em> or the other optional
arguments, use the <code class="literal">OPERATOR()</code> syntax, for example:
</p><pre class="programlisting">
COMMUTATOR = OPERATOR(myschema.===) ,
</pre></div><div class="refsect1" id="id-1.9.3.72.7"><h2>Notes</h2><p>
Refer to <a class="xref" href="xoper.html" title="38.13. User-defined Operators">Section 38.13</a> for further information.
</p><p>
It is not possible to specify an operator's lexical precedence in
<code class="command">CREATE OPERATOR</code>, because the parser's precedence behavior
is hard-wired. See <a class="xref" href="sql-syntax-lexical.html#SQL-PRECEDENCE" title="4.1.6. Operator Precedence">Section 4.1.6</a> for precedence details.
</p><p>
The obsolete options <code class="literal">SORT1</code>, <code class="literal">SORT2</code>,
<code class="literal">LTCMP</code>, and <code class="literal">GTCMP</code> were formerly used to
specify the names of sort operators associated with a merge-joinable
operator. This is no longer necessary, since information about
associated operators is found by looking at B-tree operator families
instead. If one of these options is given, it is ignored except
for implicitly setting <code class="literal">MERGES</code> true.
</p><p>
Use <a class="xref" href="sql-dropoperator.html" title="DROP OPERATOR"><span class="refentrytitle">DROP OPERATOR</span></a> to delete user-defined operators
from a database. Use <a class="xref" href="sql-alteroperator.html" title="ALTER OPERATOR"><span class="refentrytitle">ALTER OPERATOR</span></a> to modify operators in a
database.
</p></div><div class="refsect1" id="id-1.9.3.72.8"><h2>Examples</h2><p>
The following command defines a new operator, area-equality, for
the data type <code class="type">box</code>:
</p><pre class="programlisting">
CREATE OPERATOR === (
LEFTARG = box,
RIGHTARG = box,
FUNCTION = area_equal_function,
COMMUTATOR = ===,
NEGATOR = !==,
RESTRICT = area_restriction_function,
JOIN = area_join_function,
HASHES, MERGES
);
</pre></div><div class="refsect1" id="id-1.9.3.72.9"><h2>Compatibility</h2><p>
<code class="command">CREATE OPERATOR</code> is a
<span class="productname">PostgreSQL</span> extension. There are no
provisions for user-defined operators in the SQL standard.
</p></div><div class="refsect1" id="id-1.9.3.72.10"><h2>See Also</h2><span class="simplelist"><a class="xref" href="sql-alteroperator.html" title="ALTER OPERATOR"><span class="refentrytitle">ALTER OPERATOR</span></a>, <a class="xref" href="sql-createopclass.html" title="CREATE OPERATOR CLASS"><span class="refentrytitle">CREATE OPERATOR CLASS</span></a>, <a class="xref" href="sql-dropoperator.html" title="DROP OPERATOR"><span class="refentrytitle">DROP OPERATOR</span></a></span></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="sql-creatematerializedview.html">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="sql-commands.html">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="sql-createopclass.html">Next</a></td></tr><tr><td width="40%" align="left" valign="top">CREATE MATERIALIZED VIEW </td><td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td><td width="40%" align="right" valign="top"> CREATE OPERATOR CLASS</td></tr></table></div></body></html>
