Operators

operators Operators#

Operators (PostgreSQL)#

In PostgreSQL, an operator is a symbol or keyword that performs an operation between values.

Examples you already know:

=
>
<
+
||
@>
->

PostgreSQL treats operators as first-class database objects, not just syntax.

That’s why you see Operators as a node in pgAdmin.

1️⃣ What makes PostgreSQL operators special?#

Unlike many databases:

  • Operators are typed

  • Operators can be overloaded

  • You can create your own operators

  • Operators are backed by functions

  • Operators can be indexed

  • Operators integrate deeply with indexes, JSON, arrays, FTS, GIS

2️⃣ Basic operator categories#

Comparison operators

=   <>   !=   <   <=   >   >=

Example:

SELECT * FROM users WHERE age >= 18;

Logical operators

AND   OR   NOT

Example:

WHERE active = true AND deleted = false;

String operators

||     -- concatenate
LIKE
ILIKE

Example:

SELECT first_name || ' ' || last_name FROM users;

3️⃣ Array operators (very PostgreSQL-specific)#

@>   contains
<@   is contained by
&&   overlaps

Example:

SELECT * FROM posts
WHERE tags @> ARRAY['database'];

4️⃣ JSON / JSONB operators (huge feature)#

->    get JSON object
->>   get text
@>    contains
?     key exists

Example:

SELECT data->>'email'
FROM users
WHERE data @> '{"verified": true}';

These operators are indexable with GIN indexes.

5️⃣ Range operators#

Used with range types (int4range, tsrange, etc.)

@>   contains
&&   overlaps

Example:

SELECT *
FROM bookings
WHERE period && tsrange('2025-01-01', '2025-01-10');

6️⃣ Pattern matching operators#

~     regex match
~*    regex match (case-insensitive)
!~    regex NOT match

Example:

SELECT *
FROM users
WHERE email ~* '^[a-z0-9._%+-]+@example\.com$';

7️⃣ Why Operators appear in pgAdmin#

Because operators are stored in system catalogs:

  • pg_operator

  • Linked to:

  • left type

  • right type

  • result type

  • implementation function

Example (simplified):

SELECT *
FROM pg_operator
WHERE oprname = '@>';

8️⃣ Creating your own operator (advanced)#

PostgreSQL allows custom operators.

Step 1: Create a function

CREATE FUNCTION int_is_even(int)
RETURNS boolean
LANGUAGE sql
AS $$
    SELECT $1 % 2 = 0;
$$;

Step 2: Create operator

CREATE OPERATOR ## (
    RIGHTARG = int,
    PROCEDURE = int_is_even
);

Use it

SELECT 10 ##;  -- true
SELECT 7  ##;  -- false

This is why operators are real database objects.

9️⃣ Operators + Indexes (important)#

PostgreSQL indexes support specific operators.

Example:

  • B-tree supports: = < > <= >=

  • GIN supports: @> ? &&

  • GiST supports: spatial and range operators

This is why operator choice affects performance.

🔟 When should you care deeply about operators?#

You should care if you are:

  • Designing APIs directly on PostgreSQL

  • Using JSONB heavily

  • Using arrays or ranges

  • Building search systems

  • Using PostgREST or Hasura

  • Designing custom domains and constraints

  • Optimizing query performance

Summary

Operators in PostgreSQL are typed, index-aware, extensible database objects that define how values interact.

Contents