<!--
doc/src/sgml/ref/create_operator.sgml
PostgreSQL documentation
-->

<refentry id="sql-createoperator">
 <indexterm zone="sql-createoperator">
  <primary>CREATE OPERATOR</primary>
 </indexterm>

 <refmeta>
  <refentrytitle>CREATE OPERATOR</refentrytitle>
  <manvolnum>7</manvolnum>
  <refmiscinfo>SQL - Language Statements</refmiscinfo>
 </refmeta>

 <refnamediv>
  <refname>CREATE OPERATOR</refname>
  <refpurpose>define a new operator</refpurpose>
 </refnamediv>

 <refsynopsisdiv>
<synopsis>
CREATE OPERATOR <replaceable>name</replaceable> (
    {FUNCTION|PROCEDURE} = <replaceable class="parameter">function_name</replaceable>
    [, LEFTARG = <replaceable class="parameter">left_type</replaceable> ] [, RIGHTARG = <replaceable class="parameter">right_type</replaceable> ]
    [, COMMUTATOR = <replaceable class="parameter">com_op</replaceable> ] [, NEGATOR = <replaceable class="parameter">neg_op</replaceable> ]
    [, RESTRICT = <replaceable class="parameter">res_proc</replaceable> ] [, JOIN = <replaceable class="parameter">join_proc</replaceable> ]
    [, HASHES ] [, MERGES ]
)
</synopsis>
 </refsynopsisdiv>

 <refsect1>
  <title>Description</title>

  <para>
   <command>CREATE OPERATOR</command> defines a new operator,
   <replaceable class="parameter">name</replaceable>.  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.
  </para>

  <para>
   The operator name is a sequence of up to <symbol>NAMEDATALEN</symbol>-1
   (63 by default) characters from the following list:
<literallayout>
+ - * / &lt; &gt; = ~ ! @ # % ^ &amp; | ` ?
</literallayout>

   There are a few restrictions on your choice of name:
   <itemizedlist>
    <listitem>
     <para>
     <literal>--</literal> and <literal>/*</literal> cannot appear anywhere in an operator name,
     since they will be taken as the start of a comment.
     </para>
    </listitem>
    <listitem>
     <para>
     A multicharacter operator name cannot end in <literal>+</literal> or
     <literal>-</literal>,
     unless the name also contains at least one of these characters:
<literallayout>
~ ! @ # % ^ &amp; | ` ?
</literallayout>
     For example, <literal>@-</literal> is an allowed operator name,
     but <literal>*-</literal> is not.
     This restriction allows <productname>PostgreSQL</productname> to
     parse SQL-compliant commands without requiring spaces between tokens.
     </para>
    </listitem>
    <listitem>
     <para>
     The symbol <literal>=&gt;</literal> is reserved by the SQL grammar,
     so it cannot be used as an operator name.
     </para>
    </listitem>
   </itemizedlist>
  </para>

  <para>
   The operator <literal>!=</literal> is mapped to
   <literal>&lt;&gt;</literal> on input, so these two names are always
   equivalent.
  </para>

  <para>
   For binary operators, both <literal>LEFTARG</literal> and
   <literal>RIGHTARG</literal> must be defined.  For prefix operators only
   <literal>RIGHTARG</literal> should be defined.
   The <replaceable class="parameter">function_name</replaceable>
   function must have been previously defined using <command>CREATE
   FUNCTION</command> and must be defined to accept the correct number
   of arguments (either one or two) of the indicated types.
  </para>

  <para>
   In the syntax of <literal>CREATE OPERATOR</literal>, the keywords
   <literal>FUNCTION</literal> and <literal>PROCEDURE</literal> are
   equivalent, but the referenced function must in any case be a function, not
   a procedure.  The use of the keyword <literal>PROCEDURE</literal> here is
   historical and deprecated.
  </para>

  <para>
   The other clauses specify optional operator optimization attributes.
   Their meaning is detailed in <xref linkend="xoper-optimization"/>.
  </para>

  <para>
   To be able to create an operator, you must have <literal>USAGE</literal>
   privilege on the argument types and the return