3.22. Operators

Operators are one of the most powerful objects implemented by PostgreSQL as an extension of the SQL standards. Despite the enormous set of built-in operators available in a default installation, the user is able to create custom operators as done for functions and languages, for instance.

An operator can be unary or binary depending on the arguments specified for it, moreover, all the logic of this object is stored in a user-defined function. Additionally, operators can be overloaded, meaning that the same operator can handle a different set of data types.

It's quite simple to create an operator but there are some rules that must be observed in order to avoid errors. First, operator names should be composed by one or more of the following characters +-*/ <> =~!@#%^&|‘? as stated by the documentation. Second, one or two data types must be specified as arguments of the operator. Finally, the function that defines the operator must have a specific signature according to the types of the arguments.

Attribute Description
Operator Function Defines the function that will perform the operator's logic. This function must have the signature: void function(typeA, typeB) where typeA is the same as the one configured in the Left Argument Type field and typeB the same type as Right Argument Type.
Options Options used for the operator. MERGES indicates that the operator supports a merge join. HASHES indicates that the operator supports a hash join. Those options are fully detailed on PostgreSQL documentation.
Left Argument Type Configures the left argument data type. If you intend to create a left unary operator just configure this field with the any data type.
Right Argument Type Configures the right argument data type. If you intend to create a right unary operator just configure this field with the any data type.
Advanced This tab reunites fields used as advanced options for the operator being configured.
Join This object picker defines a function to be used as an estimation function for the operator.
Restrict This object picker defines a function to be used as the restriction selectively function for the operator.
Commutator This object picker defines a previously configured operator to be used as the commutator for the object being configured.
Negator This object picker defines a previously configured operator to be used as the negator for the object being configured.

Jan 14, 2022 at 11:50