| Server IP : 104.21.25.180 / Your IP : 162.159.115.41 Web Server : Apache/2.4.37 System : Linux almalinux.duckdns.org 4.18.0-553.111.1.el8_10.x86_64 #1 SMP Sun Mar 8 20:06:07 EDT 2026 x86_64 User : ricodeal ( 1046) PHP Version : 7.4.33 Disable Function : NONE MySQL : OFF | cURL : ON | WGET : ON | Perl : ON | Python : ON | Sudo : ON | Pkexec : ON Directory : /usr/share/doc/postgresql-docs/html/ |
Upload File : |
<?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="[email protected]" /><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 10.23 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.69.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> (
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.69.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>+ - * / < > = ~ ! @ # % ^ & | ` ?</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>~ ! @ # % ^ & | ` ?</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><div class="note"><h3 class="title">Note</h3><p> Right unary, also called postfix, operators are deprecated and will be
removed in <span class="productname">PostgreSQL</span> version 14.
</p></div><p> The <em class="replaceable"><code>function_name</code></em>
procedure 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> The other clauses specify optional operator optimization clauses.
Their meaning is detailed in <a class="xref" href="xoper-optimization.html" title="37.13. Operator Optimization Information">Section 37.13</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.69.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.69.7"><h2>Notes</h2><p> Refer to <a class="xref" href="xoper.html" title="37.12. User-defined Operators">Section 37.12</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.69.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,
PROCEDURE = area_equal_procedure,
COMMUTATOR = ===,
NEGATOR = !==,
RESTRICT = area_restriction_procedure,
JOIN = area_join_procedure,
HASHES, MERGES
);</pre></div><div class="refsect1" id="id-1.9.3.69.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.69.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 xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navfooter"><hr></hr><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="sql-creatematerializedview.html" title="CREATE MATERIALIZED VIEW">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="sql-createopclass.html" title="CREATE OPERATOR CLASS">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" title="PostgreSQL 10.23 Documentation">Home</a></td><td width="40%" align="right" valign="top"> CREATE OPERATOR CLASS</td></tr></table></div></body></html>