How to Create Custom Operators in PostgreSQL

Abdul Wahab Junaid

3 years ago

How to Create Custom Operators in PostgreSQL

One of the features that consistently surprises people who are new to PostgreSQL is that operators like +, =, and @> aren’t hardcoded magic — they’re actually just syntactic sugar mapped to underlying functions, and I can define my own. PostgreSQL’s operator system is fully extensible, which means if my application has a domain-specific concept that would benefit from its own symbolic notation — think geometric distance, custom equality semantics for a composite type, or a specialized containment check — I can build a real operator for it, not just a function I have to call with parentheses.

In this guide, I’ll explain what custom operators are, the syntax for creating them, how to define the underlying functions they rely on, practical real-world examples, common pitfalls, and best practices for keeping custom operators maintainable.

What Are Custom Operators in PostgreSQL?

A PostgreSQL operator is a symbolic alias for a function, combined with metadata that tells the query planner how the operator behaves — including whether it’s used for indexing, whether it’s commutative, and what its negator is (if any). When I write a = b, PostgreSQL internally maps that to a call to the int4eq function (or whatever the appropriate equality function is for the operand types). CREATE OPERATOR lets me define entirely new symbols, or overload existing symbols for new type combinations.

This matters most when I’m working with custom data types, extension types (like PostGIS geometries), or when I want expressive, readable query syntax for a domain-specific comparison that would otherwise require a verbose function call every time.

Basic Syntax

CREATE OPERATOR operator_symbol (
    FUNCTION = function_name,
    LEFTARG = left_type,
    RIGHTARG = right_type,
    COMMUTATOR = commutator_operator,
    NEGATOR = negator_operator
);

operator_symbol — the actual symbol, built from a limited set of characters (+ - * / < > = ~ ! @ # % ^ & | ? `), up to a certain length.
FUNCTION — the underlying function that implements the operator’s logic. This function must already exist.
LEFTARG / RIGHTARG — the data types on the left and right side of the operator. For a unary (prefix) operator, only one of these is specified.
COMMUTATOR — tells the planner that a OP b is equivalent to b OP a with a related operator, which helps query optimization.
NEGATOR — links this operator to its logical opposite, which also assists the planner, especially for indexed queries.

A Simple Practical Example

Suppose I have a products table and I want a custom operator ~= that checks whether two prices are “approximately equal” within a tolerance of 1 cent — useful for floating-point-safe comparisons.

Step 1: Create the Underlying Function

CREATE OR REPLACE FUNCTION approx_equal(a NUMERIC, b NUMERIC)
RETURNS BOOLEAN
AS $$
BEGIN
    RETURN ABS(a - b) < 0.01;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

I mark the function IMMUTABLE since its output depends only on its inputs — this is important, because PostgreSQL uses volatility markers to decide whether results can be cached or used in index expressions.

Step 2: Create the Operator

CREATE OPERATOR ~= (
    FUNCTION = approx_equal,
    LEFTARG = NUMERIC,
    RIGHTARG = NUMERIC,
    COMMUTATOR = ~=
);

Step 3: Use It

SELECT price, price ~= 19.99 AS is_close_to_price
FROM products;

SELECT 10.001 ~= 10.002;
-- t

This reads far more naturally in a WHERE clause than repeatedly calling approx_equal(price, 19.99).

Example: A Custom Operator for a Composite Type

Suppose I have a custom type representing a geographic point:

CREATE TYPE geo_point AS (
    lat NUMERIC,
    lng NUMERIC
);

I want an operator <-> that computes the straight-line distance between two points, similar to what PostGIS or the built-in geometric types offer natively.

CREATE OR REPLACE FUNCTION geo_distance(p1 geo_point, p2 geo_point)
RETURNS NUMERIC
AS $$
BEGIN
    RETURN SQRT(POWER(p1.lat - p2.lat, 2) + POWER(p1.lng - p2.lng, 2));
END;
$$ LANGUAGE plpgsql IMMUTABLE;

CREATE OPERATOR <-> (
    FUNCTION = geo_distance,
    LEFTARG = geo_point,
    RIGHTARG = geo_point,
    COMMUTATOR = <->
);

SELECT ROW(31.5, 74.3)::geo_point <-> ROW(31.6, 74.4)::geo_point;

This pattern — a custom operator wrapping a distance function — is exactly how PostgreSQL’s own built-in geometric and full-text search operators are constructed under the hood, so it’s a genuinely idiomatic use of the feature, not a hack.

Unary (Prefix) Operators

Custom operators don’t have to be binary. I can define a prefix operator by omitting LEFTARG:

CREATE OR REPLACE FUNCTION negate_custom(a NUMERIC)
RETURNS NUMERIC
AS $$
BEGIN
    RETURN -1 * a;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

CREATE OPERATOR @- (
    FUNCTION = negate_custom,
    RIGHTARG = NUMERIC
);

SELECT @- 15;
-- -15

Overloading Existing Operator Symbols for New Types

I can also reuse existing symbols like = or < for my own custom types, as long as the combination of operator symbol plus operand types doesn’t already exist:

CREATE TYPE money_range AS (
    low NUMERIC,
    high NUMERIC
);

CREATE OR REPLACE FUNCTION range_contains(r money_range, val NUMERIC)
RETURNS BOOLEAN
AS $$
BEGIN
    RETURN val BETWEEN r.low AND r.high;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

CREATE OPERATOR @> (
    FUNCTION = range_contains,
    LEFTARG = money_range,
    RIGHTARG = NUMERIC
);

SELECT ROW(100, 500)::money_range @> 250;
-- t

Reusing @> here is deliberate — it mirrors PostgreSQL’s own “contains” convention used by array, range, and JSONB types, which keeps the API intuitive for anyone already familiar with PostgreSQL idioms.

Making Operators Indexable

If I want a custom operator to be usable in index scans (for example, with a GiST or GIN index), I need to associate it with an operator class using CREATE OPERATOR CLASS. This is a more advanced step, typically only needed when building genuinely new indexable data types, similar to how PostGIS registers its own operator classes for spatial indexing. For most custom operators used purely for readable query syntax on scalar comparisons, this step isn’t necessary — the planner will simply evaluate the operator as a regular function call during a sequential scan.

Common Use Cases

Domain-specific comparisons — tolerance-based numeric equality, custom string similarity thresholds, or business-specific containment logic.
Geometric and spatial calculations — distance, containment, and intersection operators for custom coordinate or shape types.
Readable query syntax for composite types — giving custom types a natural comparison syntax instead of verbose function calls everywhere.
Domain-specific range or interval logic — custom overlap or containment checks for business concepts like date ranges, price bands, or version ranges.
Extension development — many PostgreSQL extensions (like hstore, ltree, and PostGIS) rely heavily on custom operators to provide their expressive query syntax.

Troubleshooting Common Issues

“operator does not exist” errors after creation This is almost always a type mismatch — PostgreSQL operator resolution is exact-match-first and doesn’t always implicitly cast between types the way I might expect. I check that the operand types in my query exactly match (or can be automatically cast to) the LEFTARG/RIGHTARG types I declared.

Ambiguous operator errors when multiple overloads exist If I’ve defined multiple versions of the same operator symbol for different, but implicitly castable, type combinations, PostgreSQL sometimes can’t determine which one to use. I resolve this with explicit type casts in the query (a::numeric ~= b::numeric), or by being more conservative about how many overloads I introduce for a single symbol.

Operator works but query performance is poor on large tables Without an associated operator class and index, a custom operator forces a sequential scan whenever it’s used in a WHERE clause, since PostgreSQL has no index-based shortcut for it. If this operator is used frequently on large tables, I look into building a proper operator class and GiST/GIN index, or restructure the query to filter on indexed columns first and apply the custom operator afterward on a smaller row set.

Confusing precedence with built-in operators Custom operators using symbols like @ or ~ sometimes have unexpected precedence relative to built-in arithmetic or comparison operators. I liberally use parentheses around custom operator expressions in complex queries rather than relying on inferred precedence rules.

DROP OPERATOR fails due to dependent objects If the operator is used inside a view, another function, or an operator class, PostgreSQL blocks the drop unless I use CASCADE. I check pg_depend first, or simply try the plain DROP OPERATOR and read the dependency error to see exactly what needs to be addressed before removing it.

Best Practices

Mark supporting functions with the correct volatility. IMMUTABLE for pure functions of their inputs, STABLE for functions that don’t modify the database but may depend on external state within a single statement, and VOLATILE (the default) only when genuinely necessary — incorrect volatility markers can cause subtle caching bugs or missed optimizations.
Always define COMMUTATOR and NEGATOR when they logically apply. This isn’t just documentation — it directly helps the query planner make better decisions, particularly for operators used in WHERE clauses.
Reuse familiar symbols thoughtfully. Piggybacking on conventions like @> for “contains” or <-> for “distance” (matching PostgreSQL’s own built-in geometric and range types) makes custom operators far more intuitive to other developers than inventing an unfamiliar symbol from scratch.
Document every custom operator clearly. A symbol like ~= conveys nothing about its meaning without a comment or accompanying documentation — I always keep a short reference (in a README or schema comment) explaining exactly what each custom operator does and on what types.
Avoid excessive operator proliferation. It’s tempting to create a symbol for every comparison I need, but each new operator adds cognitive overhead for anyone reading queries later. I only create one when it meaningfully improves readability over a plain function call.
Test operator behavior with NULLs explicitly. Just like standard SQL operators, I decide and test deliberately what a custom operator should return when either operand is NULL, rather than letting it fall out of the implementation by accident.
Consider a full operator class if performance matters. If a custom operator becomes a frequent WHERE clause filter on a large table, investing in a proper GiST/GIN operator class pays for itself quickly in query performance.

Dropping and Modifying Operators

Unlike functions, operators can’t be modified in place with a “CREATE OR REPLACE” equivalent — I have to drop and recreate them:

DROP OPERATOR IF EXISTS ~= (NUMERIC, NUMERIC);

CREATE OPERATOR ~= (
    FUNCTION = approx_equal,
    LEFTARG = NUMERIC,
    RIGHTARG = NUMERIC,
    COMMUTATOR = ~=
);

If other objects — views, other operators, or operator classes — depend on the operator, I need DROP OPERATOR ... CASCADE, and I always review exactly what will be affected before doing so, since cascading drops can silently remove more than intended in a complex schema.

Inspecting Existing Operators

Before creating a new operator, I always check whether PostgreSQL (or an installed extension) already defines something equivalent, since duplicating built-in functionality with a slightly different symbol just adds confusion. I use the psql meta-command:

\do ~=

Or query the system catalog directly for more detail:

SELECT oprname, oprleft::regtype, oprright::regtype, oprcode
FROM pg_operator
WHERE oprname = '~=';

This is especially useful when working with extensions like PostGIS or hstore, which define dozens of custom operators — checking pg_operator helps me understand exactly what a given symbol does for specific operand types before I either reuse it or choose something different for my own custom type.

Real-World Example: Case-Insensitive Text Equality

A genuinely practical example I’ve used in production is a case-insensitive equality operator for a citext-like use case, without requiring the citext extension:

CREATE OR REPLACE FUNCTION ci_text_equal(a TEXT, b TEXT)
RETURNS BOOLEAN
AS $$
BEGIN
    RETURN lower(a) = lower(b);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

CREATE OPERATOR ==* (
    FUNCTION = ci_text_equal,
    LEFTARG = TEXT,
    RIGHTARG = TEXT,
    COMMUTATOR = ==*,
    NEGATOR = <>*
);

SELECT 'PostgreSQL' ==* 'postgresql';
-- t

I intentionally avoided overloading the standard = operator here, since redefining behavior for an existing widely-used symbol on a common type like TEXT could have confusing, far-reaching consequences across the entire database — a good general rule is to reserve symbol reuse for genuinely new or narrowly scoped custom types, not built-in types with well-established default behavior.

Frequently Asked Questions

Can I create an operator that works between two completely different types? Yes — LEFTARG and RIGHTARG can be any two distinct (or identical) types, as long as an appropriate function exists accepting those two types in that order. This is exactly how operators like jsonb @> jsonb or tsvector @@ tsquery work internally.

Do custom operators support ANY/ALL array syntax automatically? Yes — once a scalar operator like = or a custom equivalent is defined for a type, PostgreSQL’s = ANY(array) and = ALL(array) syntax works automatically, since these constructs are built on top of the underlying scalar operator rather than being separately defined.

Is there a performance cost to using a custom operator versus calling the function directly? No meaningful difference — a custom operator is simply syntactic sugar resolving to the same underlying function call, so a ~= b and approx_equal(a, b) perform identically under the hood.

A Quick Reference for Common Symbol Conventions

Since PostgreSQL’s own extensions already establish informal conventions for what certain symbols mean, I try to stick with them rather than inventing something unfamiliar:

@> and <@ generally mean “contains” and “is contained by,” following the convention used by arrays, ranges, and JSONB.
<-> generally means “distance between,” following the convention used by PostgreSQL’s built-in geometric types and extensions like PostGIS and pg_trgm.
~ and ~* generally mean pattern matching (case-sensitive and case-insensitive respectively), following the convention used by regular expression matching.
&& generally means “overlaps,” following the convention used by ranges and arrays.

Sticking to these conventions, even when defining an operator for a completely custom type, makes the resulting queries far more intuitive to anyone who already has PostgreSQL experience, since they can reasonably guess what the operator does before ever reading its definition.

When Not to Create a Custom Operator

Not every repeated comparison deserves a symbol. If a function call like is_within_tolerance(a, b) is only used in two or three places, I usually leave it as a plain function — the cognitive cost of introducing a new symbol into the team’s shared vocabulary only pays off once the comparison shows up frequently enough, or the readability gain in a WHERE clause is genuinely significant. I treat custom operators the way I’d treat introducing a new keyword into a programming language: powerful, but something to do sparingly and deliberately.

Final Thoughts

Custom operators are one of PostgreSQL’s more elegant extensibility features — they let me build genuinely expressive, domain-specific query syntax rather than settling for verbose function calls everywhere. I don’t reach for them constantly, since a new operator is a small but real addition to the vocabulary anyone reading my SQL has to learn, but when I have a genuinely common comparison or transformation tied to a custom or composite type, a well-named, well-documented operator can make queries dramatically more readable. Understanding how PostgreSQL’s operator system works under the hood also demystifies a lot of what’s happening when I use extensions like PostGIS or hstore, since they’re built on exactly this same mechanism.