{"Entry":{"collection":"sql","key":"create-operator","name":"CREATE OPERATOR","aliases":["createoperator"],"metadata":{"aliases":["createoperator"],"changed_in":["6.5","7.2","7.3","7.4","8.3","9.0","11","19"],"changes":[{"from":"6.4","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","usage"],"removed":[]},"status":"changed","synopsis":{"added":["[, HASHES ]","[, SORT1      = left_sort_op ]","[, SORT2      = right_sort_op ]"],"removed":["[, HASHES ]","[, JOIN       = join_proc ]","[, SORT       = sort_op [, ...] ]"]},"to":"6.5"},{"from":"6.5","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","compatibility"],"removed":[]},"status":"changed","synopsis":null,"to":"7.0"},{"from":"7.0","purpose_changed":false,"renamed":{"from_file":"sql-createoperator.htm","to_file":"sql-createoperator.html"},"sections":{"added":[],"changed":["description","usage"],"removed":[]},"status":"changed","synopsis":null,"to":"7.1"},{"from":"7.1","purpose_changed":true,"renamed":null,"sections":{"added":[],"changed":["description","compatibility"],"removed":[]},"status":"changed","synopsis":{"added":["[, LEFTARG = lefttype","] [, RIGHTARG = righttype ]"],"removed":["[, LEFTARG = type1 ] [, RIGHTARG = type2 ]"]},"to":"7.2"},{"from":"7.2","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","usage"],"removed":[]},"status":"changed","synopsis":{"added":["[, HASHES ] [, MERGES ]","[, SORT1 = left_sort_op ] [, SORT2 = right_sort_op ]","[, LTCMP = less_than_op ] [, GTCMP = greater_than_op ] )"],"removed":[]},"to":"7.3"},{"from":"7.3","purpose_changed":false,"renamed":null,"sections":{"added":["parameters","notes","examples"],"changed":["description","compatibility"],"removed":["usage"]},"status":"changed","synopsis":{"added":["PROCEDURE = funcname"],"removed":["CREATE OPERATOR name ( PROCEDURE = func_name"]},"to":"7.4"},{"from":"7.4","purpose_changed":false,"renamed":null,"sections":{"added":["see_also"],"changed":["description","parameters","notes"],"removed":[]},"status":"changed","synopsis":null,"to":"8.0"},{"from":"8.0","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","notes"],"removed":[]},"status":"changed","synopsis":null,"to":"8.1"},{"from":"8.1","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","notes"],"removed":[]},"status":"changed","synopsis":null,"to":"8.2"},{"from":"8.2","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","parameters","notes","examples"],"removed":[]},"status":"changed","synopsis":{"added":[],"removed":["[, SORT1 = left_sort_op ] [, SORT2 = right_sort_op ]","[, LTCMP = less_than_op ] [, GTCMP = greater_than_op ]"]},"to":"8.3"},{"from":"8.4","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","parameters","notes"],"removed":[]},"status":"changed","synopsis":{"added":["PROCEDURE = function_name","[, LEFTARG = left_type ] [, RIGHTARG = right_type ]"],"removed":["PROCEDURE = funcname","[, LEFTARG = lefttype ] [, RIGHTARG = righttype ]"]},"to":"9.0"},{"from":"9.1","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description"],"removed":[]},"status":"changed","synopsis":null,"to":"9.2"},{"from":"9.4","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description"],"removed":[]},"status":"changed","synopsis":null,"to":"9.5"},{"from":"9.5","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","notes"],"removed":[]},"status":"changed","synopsis":null,"to":"9.6"},{"from":"9.6","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","notes"],"removed":[]},"status":"changed","synopsis":null,"to":"10"},{"from":"10","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","notes","examples"],"removed":[]},"status":"changed","synopsis":{"added":["{FUNCTION|PROCEDURE} = function_name"],"removed":[]},"to":"11"},{"from":"11","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","notes"],"removed":[]},"status":"changed","synopsis":null,"to":"12"},{"from":"13","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","parameters","notes"],"removed":[]},"status":"changed","synopsis":null,"to":"14"},{"from":"16","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","notes"],"removed":[]},"status":"changed","synopsis":null,"to":"17"},{"from":"18","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description"],"removed":[]},"status":"changed","synopsis":{"added":[],"removed":["[, LEFTARG = left_type ] [, RIGHTARG = right_type ]"]},"to":"19"}],"content_hash":"0d05a6105619574193e8357b96abacaa4e63ee2e0fecbd3dcdfc525e103e6730","editorial":{},"first_version":"6.4","group":"type","imported_at":"2026-09-30T17:43:37.497295+08:00","last_version":"20","name":"CREATE OPERATOR","object":"OPERATOR","position":3011,"present_in":["6.4","6.5","7.0","7.1","7.2","7.3","7.4","8.0","8.1","8.2","8.3","8.4","9.0","9.1","9.2","9.3","9.4","9.5","9.6","10","11","12","13","14","15","16","17","18","19","20"],"purpose":"define a new operator","purpose_zh":"","related":["alter-operator","create-operator-class","drop-operator"],"slug":"create-operator","source_rev":"a709ab85","synopsis":"CREATE OPERATOR name (\n{FUNCTION|PROCEDURE} = function_name\n[, LEFTARG = left_type ] , RIGHTARG = right_type\n[, COMMUTATOR = com_op ] [, NEGATOR = neg_op ]\n[, RESTRICT = res_proc ] [, JOIN = join_proc ]\n[, HASHES ] [, MERGES ]\n)","verb":"CREATE"}},"Definition":{"Collection":"sql","Key":"create-operator","SourceDatabase":"center","Version":"18","SourceTable":"sqlcmd","SourceKey":"create-operator","SourceRevision":"a709ab85","Facts":{"anchor":"SQL-CREATEOPERATOR","file":"sql-createoperator.html","lang":"en","name":"CREATE OPERATOR","purpose":"define a new operator","purpose_zh":"","related":["alter-operator","create-operator-class","drop-operator"],"sections":[{"html":"\u003cp\u003e\u003ccode class=\"command\"\u003eCREATE OPERATOR\u003c/code\u003e defines a new operator, \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e. 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.\u003c/p\u003e\u003cp\u003eThe operator name is a sequence of up to \u003ccode class=\"symbol\"\u003eNAMEDATALEN\u003c/code\u003e-1 (63 by default) characters from the following list:\u003c/p\u003e\u003cdiv class=\"literallayout\"\u003e\u003cp\u003e\u003cbr\u003e + - * / \u0026lt; \u0026gt; = ~ ! @ # % ^ \u0026amp; | ` ?\u003cbr\u003e\u003c/p\u003e\u003c/div\u003e\u003cp\u003eThere are a few restrictions on your choice of name:\u003c/p\u003e\u003cdiv class=\"itemizedlist\"\u003e\u003cul class=\"itemizedlist\"\u003e\u003cli class=\"listitem\"\u003e\u003cp\u003e\u003ccode class=\"literal\"\u003e--\u003c/code\u003e and \u003ccode class=\"literal\"\u003e/*\u003c/code\u003e cannot appear anywhere in an operator name, since they will be taken as the start of a comment.\u003c/p\u003e\u003c/li\u003e\u003cli class=\"listitem\"\u003e\u003cp\u003eA multicharacter operator name cannot end in \u003ccode class=\"literal\"\u003e+\u003c/code\u003e or \u003ccode class=\"literal\"\u003e-\u003c/code\u003e, unless the name also contains at least one of these characters:\u003c/p\u003e\u003cdiv class=\"literallayout\"\u003e\u003cp\u003e\u003cbr\u003e ~ ! @ # % ^ \u0026amp; | ` ?\u003cbr\u003e\u003c/p\u003e\u003c/div\u003e\u003cp\u003eFor example, \u003ccode class=\"literal\"\u003e@-\u003c/code\u003e is an allowed operator name, but \u003ccode class=\"literal\"\u003e*-\u003c/code\u003e is not. This restriction allows \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e to parse SQL-compliant commands without requiring spaces between tokens.\u003c/p\u003e\u003c/li\u003e\u003cli class=\"listitem\"\u003e\u003cp\u003eThe symbol \u003ccode class=\"literal\"\u003e=\u0026gt;\u003c/code\u003e is reserved by the SQL grammar, so it cannot be used as an operator name.\u003c/p\u003e\u003c/li\u003e\u003c/ul\u003e\u003c/div\u003e\u003cp\u003eThe operator \u003ccode class=\"literal\"\u003e!=\u003c/code\u003e is mapped to \u003ccode class=\"literal\"\u003e\u0026lt;\u0026gt;\u003c/code\u003e on input, so these two names are always equivalent.\u003c/p\u003e\u003cp\u003eFor binary operators, both \u003ccode class=\"literal\"\u003eLEFTARG\u003c/code\u003e and \u003ccode class=\"literal\"\u003eRIGHTARG\u003c/code\u003e must be defined. For prefix operators only \u003ccode class=\"literal\"\u003eRIGHTARG\u003c/code\u003e should be defined. The \u003cem class=\"replaceable\"\u003e\u003ccode\u003efunction_name\u003c/code\u003e\u003c/em\u003e function must have been previously defined using \u003ccode class=\"command\"\u003eCREATE FUNCTION\u003c/code\u003e and must be defined to accept the correct number of arguments (either one or two) of the indicated types.\u003c/p\u003e\u003cp\u003eIn the syntax of \u003ccode class=\"literal\"\u003eCREATE OPERATOR\u003c/code\u003e, the keywords \u003ccode class=\"literal\"\u003eFUNCTION\u003c/code\u003e and \u003ccode class=\"literal\"\u003ePROCEDURE\u003c/code\u003e are equivalent, but the referenced function must in any case be a function, not a procedure. The use of the keyword \u003ccode class=\"literal\"\u003ePROCEDURE\u003c/code\u003e here is historical and deprecated.\u003c/p\u003e\u003cp\u003eThe other clauses specify optional operator optimization attributes. Their meaning is detailed in \u003ca href=\"/docs/18/xoper-optimization.html\" title=\"36.15. Operator Optimization Information\"\u003eSection 36.15\u003c/a\u003e.\u003c/p\u003e\u003cp\u003eTo be able to create an operator, you must have \u003ccode class=\"literal\"\u003eUSAGE\u003c/code\u003e privilege on the argument types and the return type, as well as \u003ccode class=\"literal\"\u003eEXECUTE\u003c/code\u003e privilege on the underlying function. If a commutator or negator operator is specified, you must own those operators.\u003c/p\u003e","key":"description","title":"Description"},{"html":"\u003cdiv class=\"variablelist\"\u003e\u003cdl class=\"variablelist\"\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe name of the operator to be defined. See above for allowable characters. The name can be schema-qualified, for example \u003ccode class=\"literal\"\u003eCREATE OPERATOR myschema.+ (...)\u003c/code\u003e. 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 \u003cem class=\"firstterm\"\u003eoverloading\u003c/em\u003e.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003efunction_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe function used to implement this operator.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eleft_type\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe data type of the operator's left operand, if any. This option would be omitted for a prefix operator.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eright_type\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe data type of the operator's right operand.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecom_op\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe commutator of this operator.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eneg_op\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe negator of this operator.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eres_proc\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe restriction selectivity estimator function for this operator.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ejoin_proc\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe join selectivity estimator function for this operator.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eHASHES\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eIndicates this operator can support a hash join.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eMERGES\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eIndicates this operator can support a merge join.\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e\u003cp\u003eTo give a schema-qualified operator name in \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecom_op\u003c/code\u003e\u003c/em\u003e or the other optional arguments, use the \u003ccode class=\"literal\"\u003eOPERATOR()\u003c/code\u003e syntax, for example:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eCOMMUTATOR = OPERATOR(myschema.===) ,\n\u003c/pre\u003e","key":"parameters","title":"Parameters"},{"html":"\u003cp\u003eRefer to \u003ca href=\"/docs/18/xoper.html\" title=\"36.14. User-Defined Operators\"\u003eSection 36.14\u003c/a\u003e and \u003ca href=\"/docs/18/xoper-optimization.html\" title=\"36.15. Operator Optimization Information\"\u003eSection 36.15\u003c/a\u003e for further information.\u003c/p\u003e\u003cp\u003eWhen you are defining a self-commutative operator, you just do it. When you are defining a pair of commutative operators, things are a little trickier: how can the first one to be defined refer to the other one, which you haven't defined yet? There are three solutions to this problem:\u003c/p\u003e\u003cdiv class=\"itemizedlist\"\u003e\u003cul class=\"itemizedlist\"\u003e\u003cli class=\"listitem\"\u003e\u003cp\u003eOne way is to omit the \u003ccode class=\"literal\"\u003eCOMMUTATOR\u003c/code\u003e clause in the first operator that you define, and then provide one in the second operator's definition. Since \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e knows that commutative operators come in pairs, when it sees the second definition it will automatically go back and fill in the missing \u003ccode class=\"literal\"\u003eCOMMUTATOR\u003c/code\u003e clause in the first definition.\u003c/p\u003e\u003c/li\u003e\u003cli class=\"listitem\"\u003e\u003cp\u003eAnother, more straightforward way is just to include \u003ccode class=\"literal\"\u003eCOMMUTATOR\u003c/code\u003e clauses in both definitions. When \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e processes the first definition and realizes that \u003ccode class=\"literal\"\u003eCOMMUTATOR\u003c/code\u003e refers to a nonexistent operator, the system will make a dummy entry for that operator in the system catalog. This dummy entry will have valid data only for the operator name, left and right operand types, and owner, since that's all that \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e can deduce at this point. The first operator's catalog entry will link to this dummy entry. Later, when you define the second operator, the system updates the dummy entry with the additional information from the second definition. If you try to use the dummy operator before it's been filled in, you'll just get an error message.\u003c/p\u003e\u003c/li\u003e\u003cli class=\"listitem\"\u003e\u003cp\u003eAlternatively, both operators can be defined without \u003ccode class=\"literal\"\u003eCOMMUTATOR\u003c/code\u003e clauses and then \u003ccode class=\"command\"\u003eALTER OPERATOR\u003c/code\u003e can be used to set their commutator links. It's sufficient to \u003ccode class=\"command\"\u003eALTER\u003c/code\u003e either one of the pair.\u003c/p\u003e\u003c/li\u003e\u003c/ul\u003e\u003c/div\u003e\u003cp\u003eIn all three cases, you must own both operators in order to mark them as commutators.\u003c/p\u003e\u003cp\u003ePairs of negator operators can be defined using the same methods as for commutator pairs.\u003c/p\u003e\u003cp\u003eIt is not possible to specify an operator's lexical precedence in \u003ccode class=\"command\"\u003eCREATE OPERATOR\u003c/code\u003e, because the parser's precedence behavior is hard-wired. See \u003ca href=\"/docs/18/sql-syntax-lexical.html#SQL-PRECEDENCE\" title=\"4.1.6. Operator Precedence\"\u003eSection 4.1.6\u003c/a\u003e for precedence details.\u003c/p\u003e\u003cp\u003eThe obsolete options \u003ccode class=\"literal\"\u003eSORT1\u003c/code\u003e, \u003ccode class=\"literal\"\u003eSORT2\u003c/code\u003e, \u003ccode class=\"literal\"\u003eLTCMP\u003c/code\u003e, and \u003ccode class=\"literal\"\u003eGTCMP\u003c/code\u003e 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 \u003ccode class=\"literal\"\u003eMERGES\u003c/code\u003e true.\u003c/p\u003e\u003cp\u003eUse \u003ca href=\"/docs/18/sql-dropoperator.html\" title=\"DROP OPERATOR\"\u003e\u003ccode class=\"command\"\u003eDROP OPERATOR\u003c/code\u003e\u003c/a\u003e to delete user-defined operators from a database. Use \u003ca href=\"/docs/18/sql-alteroperator.html\" title=\"ALTER OPERATOR\"\u003e\u003ccode class=\"command\"\u003eALTER OPERATOR\u003c/code\u003e\u003c/a\u003e to modify operators in a database.\u003c/p\u003e","key":"notes","title":"Notes"},{"html":"\u003cp\u003eThe following command defines a new operator, area-equality, for the data type \u003ccode class=\"type\"\u003ebox\u003c/code\u003e:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eCREATE OPERATOR === (\n    LEFTARG = box,\n    RIGHTARG = box,\n    FUNCTION = area_equal_function,\n    COMMUTATOR = ===,\n    NEGATOR = !==,\n    RESTRICT = area_restriction_function,\n    JOIN = area_join_function,\n    HASHES, MERGES\n);\n\u003c/pre\u003e","key":"examples","title":"Examples"},{"html":"\u003cp\u003e\u003ccode class=\"command\"\u003eCREATE OPERATOR\u003c/code\u003e is a \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e extension. There are no provisions for user-defined operators in the SQL standard.\u003c/p\u003e","key":"compatibility","title":"Compatibility"},{"html":"\u003cspan class=\"simplelist\"\u003e\u003ca href=\"/wiki/sql/alter-operator/?v=18\" title=\"ALTER OPERATOR\"\u003e\u003cspan class=\"refentrytitle\"\u003eALTER OPERATOR\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/create-operator-class/?v=18\" title=\"CREATE OPERATOR CLASS\"\u003e\u003cspan class=\"refentrytitle\"\u003eCREATE OPERATOR CLASS\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/drop-operator/?v=18\" title=\"DROP OPERATOR\"\u003e\u003cspan class=\"refentrytitle\"\u003eDROP OPERATOR\u003c/span\u003e\u003c/a\u003e\u003c/span\u003e","key":"see_also","title":"See Also"}],"sections_same_as":"","slug":"18","synopsis_html":"CREATE OPERATOR \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e (\n    {FUNCTION|PROCEDURE} = \u003cem class=\"replaceable\"\u003e\u003ccode\u003efunction_name\u003c/code\u003e\u003c/em\u003e\n    [, LEFTARG = \u003cem class=\"replaceable\"\u003e\u003ccode\u003eleft_type\u003c/code\u003e\u003c/em\u003e ] [, RIGHTARG = \u003cem class=\"replaceable\"\u003e\u003ccode\u003eright_type\u003c/code\u003e\u003c/em\u003e ]\n    [, COMMUTATOR = \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecom_op\u003c/code\u003e\u003c/em\u003e ] [, NEGATOR = \u003cem class=\"replaceable\"\u003e\u003ccode\u003eneg_op\u003c/code\u003e\u003c/em\u003e ]\n    [, RESTRICT = \u003cem class=\"replaceable\"\u003e\u003ccode\u003eres_proc\u003c/code\u003e\u003c/em\u003e ] [, JOIN = \u003cem class=\"replaceable\"\u003e\u003ccode\u003ejoin_proc\u003c/code\u003e\u003c/em\u003e ]\n    [, HASHES ] [, MERGES ]\n)","synopsis_text":"CREATE OPERATOR name (\n{FUNCTION|PROCEDURE} = function_name\n[, LEFTARG = left_type ] [, RIGHTARG = right_type ]\n[, COMMUTATOR = com_op ] [, NEGATOR = neg_op ]\n[, RESTRICT = res_proc ] [, JOIN = join_proc ]\n[, HASHES ] [, MERGES ]\n)"},"ManualEvidence":{},"MeasuredEvidence":{}},"Text":{"Collection":"sql","Key":"create-operator","SourceDatabase":"pgweb","Version":"18","Locale":"zh-Hans","Title":"CREATE OPERATOR","Summary":"定义一个新的操作符","BodyHTML":"\u003cpre\u003eCREATE OPERATOR name (\n{FUNCTION|PROCEDURE} = function_name\n[, LEFTARG = left_type ] [, RIGHTARG = right_type ]\n[, COMMUTATOR = com_op ] [, NEGATOR = neg_op ]\n[, RESTRICT = res_proc ] [, JOIN = join_proc ]\n[, HASHES ] [, MERGES ]\n)\u003c/pre\u003e\u003csection\u003e\u003ch2\u003e描述\u003c/h2\u003e\u003cp\u003e\u003ccode\u003eCREATE OPERATOR\u003c/code\u003e定义一个新的操作符 \u003cem\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e。定义该操作符的用户将成为其拥有者。如果给出了模式名，该操作符将在指定的模式中创建；否则将在当前模式中创建。\u003c/p\u003e\u003cp\u003e操作符名称最多可以由 \u003ccode\u003eNAMEDATALEN\u003c/code\u003e-1 个字符（默认为 63 个）组成，这些字符必须取自下列列表：\u003c/p\u003e\u003cdiv\u003e\u003cp\u003e\u003cbr\u003e + - * / \u0026lt; \u0026gt; = ~ ! @ # % ^ \u0026amp; | ` ?\u003cbr\u003e\u003c/p\u003e\u003c/div\u003e\u003cp\u003e名称选择还有一些限制：\u003c/p\u003e\u003cdiv\u003e\u003cul\u003e\u003cli\u003e\u003cp\u003e\u003ccode\u003e--\u003c/code\u003e和\u003ccode\u003e/*\u003c/code\u003e不能出现在操作符名称的任何位置，因为它们会被视为注释的开始。\u003c/p\u003e\u003c/li\u003e\u003cli\u003e\u003cp\u003e多字符操作符名称不能以\u003ccode\u003e+\u003c/code\u003e或\u003ccode\u003e-\u003c/code\u003e 结尾，除非该名称中还至少包含下列字符之一：\u003c/p\u003e\u003cdiv\u003e\u003cp\u003e\u003cbr\u003e ~ ! @ # % ^ \u0026amp; | ` ?\u003cbr\u003e\u003c/p\u003e\u003c/div\u003e\u003cp\u003e例如，\u003ccode\u003e@-\u003c/code\u003e是允许的操作符名称，而 \u003ccode\u003e*-\u003c/code\u003e则不允许。这一限制使 \u003cspan\u003ePostgreSQL\u003c/span\u003e能够在不要求词元之间必须有空格的情况下解析符合 SQL 规范的命令。\u003c/p\u003e\u003c/li\u003e\u003cli\u003e\u003cp\u003e符号\u003ccode\u003e=\u0026gt;\u003c/code\u003e被 SQL 语法保留，因此不能用作操作符名称。\u003c/p\u003e\u003c/li\u003e\u003c/ul\u003e\u003c/div\u003e\u003cp\u003e输入\u003ccode\u003e!=\u003c/code\u003e时会被映射为\u003ccode\u003e\u0026lt;\u0026gt;\u003c/code\u003e，因此这两个名称始终等效。\u003c/p\u003e\u003cp\u003e对于二元操作符，必须同时定义\u003ccode\u003eLEFTARG\u003c/code\u003e和\u003ccode\u003eRIGHTARG\u003c/code\u003e。对于前缀操作符，则只应定义\u003ccode\u003eRIGHTARG\u003c/code\u003e。\u003cem\u003e\u003ccode\u003efunction_name\u003c/code\u003e\u003c/em\u003e函数必须事先已用\u003ccode\u003eCREATE FUNCTION\u003c/code\u003e定义，并且其定义必须接受正确数量（一个或两个）的指定类型参数。\u003c/p\u003e\u003cp\u003e在\u003ccode\u003eCREATE OPERATOR\u003c/code\u003e的语法中，关键字 \u003ccode\u003eFUNCTION\u003c/code\u003e和\u003ccode\u003ePROCEDURE\u003c/code\u003e是等效的，但这里引用的对象无论如何都必须是函数而不是过程。此处使用关键字 \u003ccode\u003ePROCEDURE\u003c/code\u003e是历史遗留做法，现已弃用。\u003c/p\u003e\u003cp\u003e其他子句用于指定可选的操作符优化属性。其含义详见\u003ca href=\"/docs/18/xoper-optimization.html\" rel=\"nofollow\"\u003e第 36.15 节\u003c/a\u003e。\u003c/p\u003e\u003cp\u003e要创建操作符，你必须在参数类型和返回类型上具有 \u003ccode\u003eUSAGE\u003c/code\u003e权限，并在底层函数上具有 \u003ccode\u003eEXECUTE\u003c/code\u003e权限。如果指定了交换子或求反器操作符，你还必须拥有这些操作符。\u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e参数\u003c/h2\u003e\u003cdiv\u003e\u003cdl\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e要定义的操作符名称。允许使用的字符请见上文。该名称可以带模式限定，例如\u003ccode\u003eCREATE OPERATOR myschema.+ (...)\u003c/code\u003e。如果不带模式限定，该操作符将在当前模式中创建。同一模式中的两个操作符如果针对不同的数据类型进行操作，可以具有相同的名称。这被称为\u003cem\u003e重载\u003c/em\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003efunction_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e用于实现该操作符的函数。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003eleft_type\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e该操作符左操作数的数据类型（如果有）。对于前缀操作符，应省略此选项。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003eright_type\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e该操作符右操作数的数据类型。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003ecom_op\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e该操作符的交换子。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003eneg_op\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e该操作符的求反器。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003eres_proc\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e该操作符的限制选择率估计函数。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003ejoin_proc\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e该操作符的连接选择率估算函数。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eHASHES\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e表示该操作符可以支持哈希连接。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eMERGES\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e表示该操作符可以支持归并连接。\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e\u003cp\u003e要在\u003cem\u003e\u003ccode\u003ecom_op\u003c/code\u003e\u003c/em\u003e或其他可选参数中给出带模式限定的操作符名称，请使用\u003ccode\u003eOPERATOR()\u003c/code\u003e语法，例如：\u003c/p\u003e\u003cpre\u003eCOMMUTATOR = OPERATOR(myschema.===) ,\n\u003c/pre\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e注解\u003c/h2\u003e\u003cp\u003e更多信息请参见\u003ca href=\"/docs/18/xoper.html\" rel=\"nofollow\"\u003e第 36.14 节\u003c/a\u003e和\u003ca href=\"/docs/18/xoper-optimization.html\" rel=\"nofollow\"\u003e第 36.15 节\u003c/a\u003e。\u003c/p\u003e\u003cp\u003e当你定义一个自交换操作符时，直接定义即可。定义一对互为交换子的操作符时则稍微复杂一些：先定义的那个操作符如何引用另一个尚未定义的操作符呢？这个问题有三种解决办法：\u003c/p\u003e\u003cdiv\u003e\u003cul\u003e\u003cli\u003e\u003cp\u003e一种方法是在先定义的第一个操作符中省略 \u003ccode\u003eCOMMUTATOR\u003c/code\u003e子句，然后在第二个操作符的定义中提供它。由于\u003cspan\u003ePostgreSQL\u003c/span\u003e知道交换操作符总是成对出现的，所以当它看到第二个定义时，会自动回过头去补上第一个定义中缺失的\u003ccode\u003eCOMMUTATOR\u003c/code\u003e子句。\u003c/p\u003e\u003c/li\u003e\u003cli\u003e\u003cp\u003e另一种更直接的方法是在两个定义中都包含 \u003ccode\u003eCOMMUTATOR\u003c/code\u003e子句。当 \u003cspan\u003ePostgreSQL\u003c/span\u003e处理第一个定义并发现 \u003ccode\u003eCOMMUTATOR\u003c/code\u003e引用了一个尚不存在的操作符时，系统会在系统目录中为该操作符建立一个虚拟条目。这个虚拟条目中只有操作符名称、左右操作数类型以及拥有者这些信息是有效的，因为此时 \u003cspan\u003ePostgreSQL\u003c/span\u003e只能推断出这些内容。第一个操作符的目录条目会链接到这个虚拟条目。以后在定义第二个操作符时，系统会用第二个定义中的附加信息更新这个虚拟条目。如果在该虚拟操作符被补全之前尝试使用它，只会得到一条错误消息。\u003c/p\u003e\u003c/li\u003e\u003cli\u003e\u003cp\u003e或者，也可以在定义这两个操作符时都不指定 \u003ccode\u003eCOMMUTATOR\u003c/code\u003e子句，之后再用 \u003ccode\u003eALTER OPERATOR\u003c/code\u003e设置它们的交换子链接。对其中任意一个执行\u003ccode\u003eALTER\u003c/code\u003e即可。\u003c/p\u003e\u003c/li\u003e\u003c/ul\u003e\u003c/div\u003e\u003cp\u003e在上述三种情况下，若要将两个操作符标记为交换子，必须同时拥有它们。\u003c/p\u003e\u003cp\u003e成对的求反器操作符也可以用与交换子对相同的方法定义。\u003c/p\u003e\u003cp\u003e无法在\u003ccode\u003eCREATE OPERATOR\u003c/code\u003e中指定一个操作符的词法优先级，因为解析器的优先级行为是硬写在代码中的。详见\u003ca href=\"/docs/18/sql-syntax-lexical.html#SQL-PRECEDENCE\" rel=\"nofollow\"\u003e第 4.1.6 节\u003c/a\u003e。\u003c/p\u003e\u003cp\u003e废弃的选项\u003ccode\u003eSORT1\u003c/code\u003e、\u003ccode\u003eSORT2\u003c/code\u003e、\u003ccode\u003eLTCMP\u003c/code\u003e以及\u003ccode\u003eGTCMP\u003c/code\u003e以前被用来指定与可归并连接的操作符相关联的排序操作符名称。现在已不再需要这样做，因为相关操作符的信息会通过查找 B-树操作符族来获得。如果给出这些选项中的任意一个，除了会隐式将\u003ccode\u003eMERGES\u003c/code\u003e设为真之外，其余都会被忽略。\u003c/p\u003e\u003cp\u003e使用\u003ca href=\"/docs/18/sql-dropoperator.html\" title=\"DROP OPERATOR\" rel=\"nofollow\"\u003e\u003ccode\u003eDROP OPERATOR\u003c/code\u003e\u003c/a\u003e从数据库中删除用户定义的操作符。使用\u003ca href=\"/docs/18/sql-alteroperator.html\" title=\"ALTER OPERATOR\" rel=\"nofollow\"\u003e\u003ccode\u003eALTER OPERATOR\u003c/code\u003e\u003c/a\u003e修改数据库中的操作符。\u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e示例\u003c/h2\u003e\u003cp\u003e下面的命令为数据类型\u003ccode\u003ebox\u003c/code\u003e定义了一个新的面积相等操作符：\u003c/p\u003e\u003cpre\u003eCREATE OPERATOR === (\n    LEFTARG = box,\n    RIGHTARG = box,\n    FUNCTION = area_equal_function,\n    COMMUTATOR = ===,\n    NEGATOR = !==,\n    RESTRICT = area_restriction_function,\n    JOIN = area_join_function,\n    HASHES, MERGES\n);\n\u003c/pre\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e兼容性\u003c/h2\u003e\u003cp\u003e\u003ccode\u003eCREATE OPERATOR\u003c/code\u003e是 \u003cspan\u003ePostgreSQL\u003c/span\u003e扩展。在 SQL 标准中没有用户定义操作符的规定。\u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e另见\u003c/h2\u003e\u003cspan\u003e\u003ca href=\"/wiki/sql/alter-operator/?v=18\" title=\"ALTER OPERATOR\" rel=\"nofollow\"\u003e\u003cspan\u003eALTER OPERATOR\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/create-operator-class/?v=18\" title=\"CREATE OPERATOR CLASS\" rel=\"nofollow\"\u003e\u003cspan\u003eCREATE OPERATOR CLASS\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/drop-operator/?v=18\" title=\"DROP OPERATOR\" rel=\"nofollow\"\u003e\u003cspan\u003eDROP OPERATOR\u003c/span\u003e\u003c/a\u003e\u003c/span\u003e\u003c/section\u003e","SourceRevision":"1b5ca64c","ContentHash":"677613f1a685c42cdc03bc27737c79e0e3ef726851ebf89e268a4eda4adfa509","Payload":{"purpose_zh":"定义一个新的操作符","sections":[{"html":"\u003cp\u003e\u003ccode class=\"command\"\u003eCREATE OPERATOR\u003c/code\u003e定义一个新的操作符 \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e。定义该操作符的用户将成为其拥有者。如果给出了模式名，该操作符将在指定的模式中创建；否则将在当前模式中创建。\u003c/p\u003e\u003cp\u003e操作符名称最多可以由 \u003ccode class=\"symbol\"\u003eNAMEDATALEN\u003c/code\u003e-1 个字符（默认为 63 个）组成，这些字符必须取自下列列表：\u003c/p\u003e\u003cdiv class=\"literallayout\"\u003e\u003cp\u003e\u003cbr\u003e + - * / \u0026lt; \u0026gt; = ~ ! @ # % ^ \u0026amp; | ` ?\u003cbr\u003e\u003c/p\u003e\u003c/div\u003e\u003cp\u003e名称选择还有一些限制：\u003c/p\u003e\u003cdiv class=\"itemizedlist\"\u003e\u003cul class=\"itemizedlist\"\u003e\u003cli class=\"listitem\"\u003e\u003cp\u003e\u003ccode class=\"literal\"\u003e--\u003c/code\u003e和\u003ccode class=\"literal\"\u003e/*\u003c/code\u003e不能出现在操作符名称的任何位置，因为它们会被视为注释的开始。\u003c/p\u003e\u003c/li\u003e\u003cli class=\"listitem\"\u003e\u003cp\u003e多字符操作符名称不能以\u003ccode class=\"literal\"\u003e+\u003c/code\u003e或\u003ccode class=\"literal\"\u003e-\u003c/code\u003e 结尾，除非该名称中还至少包含下列字符之一：\u003c/p\u003e\u003cdiv class=\"literallayout\"\u003e\u003cp\u003e\u003cbr\u003e ~ ! @ # % ^ \u0026amp; | ` ?\u003cbr\u003e\u003c/p\u003e\u003c/div\u003e\u003cp\u003e例如，\u003ccode class=\"literal\"\u003e@-\u003c/code\u003e是允许的操作符名称，而 \u003ccode class=\"literal\"\u003e*-\u003c/code\u003e则不允许。这一限制使 \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e能够在不要求词元之间必须有空格的情况下解析符合 SQL 规范的命令。\u003c/p\u003e\u003c/li\u003e\u003cli class=\"listitem\"\u003e\u003cp\u003e符号\u003ccode class=\"literal\"\u003e=\u0026gt;\u003c/code\u003e被 SQL 语法保留，因此不能用作操作符名称。\u003c/p\u003e\u003c/li\u003e\u003c/ul\u003e\u003c/div\u003e\u003cp\u003e输入\u003ccode class=\"literal\"\u003e!=\u003c/code\u003e时会被映射为\u003ccode class=\"literal\"\u003e\u0026lt;\u0026gt;\u003c/code\u003e，因此这两个名称始终等效。\u003c/p\u003e\u003cp\u003e对于二元操作符，必须同时定义\u003ccode class=\"literal\"\u003eLEFTARG\u003c/code\u003e和\u003ccode class=\"literal\"\u003eRIGHTARG\u003c/code\u003e。对于前缀操作符，则只应定义\u003ccode class=\"literal\"\u003eRIGHTARG\u003c/code\u003e。\u003cem class=\"replaceable\"\u003e\u003ccode\u003efunction_name\u003c/code\u003e\u003c/em\u003e函数必须事先已用\u003ccode class=\"command\"\u003eCREATE FUNCTION\u003c/code\u003e定义，并且其定义必须接受正确数量（一个或两个）的指定类型参数。\u003c/p\u003e\u003cp\u003e在\u003ccode class=\"literal\"\u003eCREATE OPERATOR\u003c/code\u003e的语法中，关键字 \u003ccode class=\"literal\"\u003eFUNCTION\u003c/code\u003e和\u003ccode class=\"literal\"\u003ePROCEDURE\u003c/code\u003e是等效的，但这里引用的对象无论如何都必须是函数而不是过程。此处使用关键字 \u003ccode class=\"literal\"\u003ePROCEDURE\u003c/code\u003e是历史遗留做法，现已弃用。\u003c/p\u003e\u003cp\u003e其他子句用于指定可选的操作符优化属性。其含义详见\u003ca href=\"/docs/18/xoper-optimization.html\" title=\"36.15. 操作符优化信息\"\u003e第 36.15 节\u003c/a\u003e。\u003c/p\u003e\u003cp\u003e要创建操作符，你必须在参数类型和返回类型上具有 \u003ccode class=\"literal\"\u003eUSAGE\u003c/code\u003e权限，并在底层函数上具有 \u003ccode class=\"literal\"\u003eEXECUTE\u003c/code\u003e权限。如果指定了交换子或求反器操作符，你还必须拥有这些操作符。\u003c/p\u003e","key":"description","title":"描述"},{"html":"\u003cdiv class=\"variablelist\"\u003e\u003cdl class=\"variablelist\"\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e要定义的操作符名称。允许使用的字符请见上文。该名称可以带模式限定，例如\u003ccode class=\"literal\"\u003eCREATE OPERATOR myschema.+ (...)\u003c/code\u003e。如果不带模式限定，该操作符将在当前模式中创建。同一模式中的两个操作符如果针对不同的数据类型进行操作，可以具有相同的名称。这被称为\u003cem class=\"firstterm\"\u003e重载\u003c/em\u003e。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003efunction_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e用于实现该操作符的函数。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eleft_type\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e该操作符左操作数的数据类型（如果有）。对于前缀操作符，应省略此选项。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eright_type\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e该操作符右操作数的数据类型。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecom_op\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e该操作符的交换子。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eneg_op\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e该操作符的求反器。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eres_proc\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e该操作符的限制选择率估计函数。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ejoin_proc\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e该操作符的连接选择率估算函数。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eHASHES\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e表示该操作符可以支持哈希连接。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eMERGES\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e表示该操作符可以支持归并连接。\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e\u003cp\u003e要在\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecom_op\u003c/code\u003e\u003c/em\u003e或其他可选参数中给出带模式限定的操作符名称，请使用\u003ccode class=\"literal\"\u003eOPERATOR()\u003c/code\u003e语法，例如：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eCOMMUTATOR = OPERATOR(myschema.===) ,\n\u003c/pre\u003e","key":"parameters","title":"参数"},{"html":"\u003cp\u003e更多信息请参见\u003ca href=\"/docs/18/xoper.html\" title=\"36.14. 用户定义的操作符\"\u003e第 36.14 节\u003c/a\u003e和\u003ca href=\"/docs/18/xoper-optimization.html\" title=\"36.15. 操作符优化信息\"\u003e第 36.15 节\u003c/a\u003e。\u003c/p\u003e\u003cp\u003e当你定义一个自交换操作符时，直接定义即可。定义一对互为交换子的操作符时则稍微复杂一些：先定义的那个操作符如何引用另一个尚未定义的操作符呢？这个问题有三种解决办法：\u003c/p\u003e\u003cdiv class=\"itemizedlist\"\u003e\u003cul class=\"itemizedlist\"\u003e\u003cli class=\"listitem\"\u003e\u003cp\u003e一种方法是在先定义的第一个操作符中省略 \u003ccode class=\"literal\"\u003eCOMMUTATOR\u003c/code\u003e子句，然后在第二个操作符的定义中提供它。由于\u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e知道交换操作符总是成对出现的，所以当它看到第二个定义时，会自动回过头去补上第一个定义中缺失的\u003ccode class=\"literal\"\u003eCOMMUTATOR\u003c/code\u003e子句。\u003c/p\u003e\u003c/li\u003e\u003cli class=\"listitem\"\u003e\u003cp\u003e另一种更直接的方法是在两个定义中都包含 \u003ccode class=\"literal\"\u003eCOMMUTATOR\u003c/code\u003e子句。当 \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e处理第一个定义并发现 \u003ccode class=\"literal\"\u003eCOMMUTATOR\u003c/code\u003e引用了一个尚不存在的操作符时，系统会在系统目录中为该操作符建立一个虚拟条目。这个虚拟条目中只有操作符名称、左右操作数类型以及拥有者这些信息是有效的，因为此时 \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e只能推断出这些内容。第一个操作符的目录条目会链接到这个虚拟条目。以后在定义第二个操作符时，系统会用第二个定义中的附加信息更新这个虚拟条目。如果在该虚拟操作符被补全之前尝试使用它，只会得到一条错误消息。\u003c/p\u003e\u003c/li\u003e\u003cli class=\"listitem\"\u003e\u003cp\u003e或者，也可以在定义这两个操作符时都不指定 \u003ccode class=\"literal\"\u003eCOMMUTATOR\u003c/code\u003e子句，之后再用 \u003ccode class=\"command\"\u003eALTER OPERATOR\u003c/code\u003e设置它们的交换子链接。对其中任意一个执行\u003ccode class=\"command\"\u003eALTER\u003c/code\u003e即可。\u003c/p\u003e\u003c/li\u003e\u003c/ul\u003e\u003c/div\u003e\u003cp\u003e在上述三种情况下，若要将两个操作符标记为交换子，必须同时拥有它们。\u003c/p\u003e\u003cp\u003e成对的求反器操作符也可以用与交换子对相同的方法定义。\u003c/p\u003e\u003cp\u003e无法在\u003ccode class=\"command\"\u003eCREATE OPERATOR\u003c/code\u003e中指定一个操作符的词法优先级，因为解析器的优先级行为是硬写在代码中的。详见\u003ca href=\"/docs/18/sql-syntax-lexical.html#SQL-PRECEDENCE\" title=\"4.1.6. 操作符优先级\"\u003e第 4.1.6 节\u003c/a\u003e。\u003c/p\u003e\u003cp\u003e废弃的选项\u003ccode class=\"literal\"\u003eSORT1\u003c/code\u003e、\u003ccode class=\"literal\"\u003eSORT2\u003c/code\u003e、\u003ccode class=\"literal\"\u003eLTCMP\u003c/code\u003e以及\u003ccode class=\"literal\"\u003eGTCMP\u003c/code\u003e以前被用来指定与可归并连接的操作符相关联的排序操作符名称。现在已不再需要这样做，因为相关操作符的信息会通过查找 B-树操作符族来获得。如果给出这些选项中的任意一个，除了会隐式将\u003ccode class=\"literal\"\u003eMERGES\u003c/code\u003e设为真之外，其余都会被忽略。\u003c/p\u003e\u003cp\u003e使用\u003ca href=\"/docs/18/sql-dropoperator.html\" title=\"DROP OPERATOR\"\u003e\u003ccode class=\"command\"\u003eDROP OPERATOR\u003c/code\u003e\u003c/a\u003e从数据库中删除用户定义的操作符。使用\u003ca href=\"/docs/18/sql-alteroperator.html\" title=\"ALTER OPERATOR\"\u003e\u003ccode class=\"command\"\u003eALTER OPERATOR\u003c/code\u003e\u003c/a\u003e修改数据库中的操作符。\u003c/p\u003e","key":"notes","title":"注解"},{"html":"\u003cp\u003e下面的命令为数据类型\u003ccode class=\"type\"\u003ebox\u003c/code\u003e定义了一个新的面积相等操作符：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eCREATE OPERATOR === (\n    LEFTARG = box,\n    RIGHTARG = box,\n    FUNCTION = area_equal_function,\n    COMMUTATOR = ===,\n    NEGATOR = !==,\n    RESTRICT = area_restriction_function,\n    JOIN = area_join_function,\n    HASHES, MERGES\n);\n\u003c/pre\u003e","key":"examples","title":"示例"},{"html":"\u003cp\u003e\u003ccode class=\"command\"\u003eCREATE OPERATOR\u003c/code\u003e是 \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e扩展。在 SQL 标准中没有用户定义操作符的规定。\u003c/p\u003e","key":"compatibility","title":"兼容性"},{"html":"\u003cspan class=\"simplelist\"\u003e\u003ca href=\"/wiki/sql/alter-operator/?v=18\" title=\"ALTER OPERATOR\"\u003e\u003cspan class=\"refentrytitle\"\u003eALTER OPERATOR\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/create-operator-class/?v=18\" title=\"CREATE OPERATOR CLASS\"\u003e\u003cspan class=\"refentrytitle\"\u003eCREATE OPERATOR CLASS\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/drop-operator/?v=18\" title=\"DROP OPERATOR\"\u003e\u003cspan class=\"refentrytitle\"\u003eDROP OPERATOR\u003c/span\u003e\u003c/a\u003e\u003c/span\u003e","key":"see_also","title":"另见"}],"sections_same_as":"","synopsis_html":"CREATE OPERATOR \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e (\n    {FUNCTION|PROCEDURE} = \u003cem class=\"replaceable\"\u003e\u003ccode\u003efunction_name\u003c/code\u003e\u003c/em\u003e\n    [, LEFTARG = \u003cem class=\"replaceable\"\u003e\u003ccode\u003eleft_type\u003c/code\u003e\u003c/em\u003e ] [, RIGHTARG = \u003cem class=\"replaceable\"\u003e\u003ccode\u003eright_type\u003c/code\u003e\u003c/em\u003e ]\n    [, COMMUTATOR = \u003cem class=\"replaceable\"\u003e\u003ccode\u003ecom_op\u003c/code\u003e\u003c/em\u003e ] [, NEGATOR = \u003cem class=\"replaceable\"\u003e\u003ccode\u003eneg_op\u003c/code\u003e\u003c/em\u003e ]\n    [, RESTRICT = \u003cem class=\"replaceable\"\u003e\u003ccode\u003eres_proc\u003c/code\u003e\u003c/em\u003e ] [, JOIN = \u003cem class=\"replaceable\"\u003e\u003ccode\u003ejoin_proc\u003c/code\u003e\u003c/em\u003e ]\n    [, HASHES ] [, MERGES ]\n)","synopsis_text":"CREATE OPERATOR name (\n{FUNCTION|PROCEDURE} = function_name\n[, LEFTARG = left_type ] [, RIGHTARG = right_type ]\n[, COMMUTATOR = com_op ] [, NEGATOR = neg_op ]\n[, RESTRICT = res_proc ] [, JOIN = join_proc ]\n[, HASHES ] [, MERGES ]\n)"}},"RequestedLocale":"zh-Hans","Fallback":false,"Versions":["10","11","12","13","14","15","16","17","18","19","20","6.4","6.5","7.0","7.1","7.2","7.3","7.4","8.0","8.1","8.2","8.3","8.4","9.0","9.1","9.2","9.3","9.4","9.5","9.6"],"Locales":["en","zh-Hans"],"Signatures":null,"Spellings":null,"SQLState":null,"Evidence":null}